4 ms·

I'm pretty much on board with these points. Here's an interesting explanation of the absence of hints: http://it.toolbox.com/blogs/database-soup/why-postgresql

by ibejoeb 16y ago

I'm pretty much on board with these points. Here's an interesting explanation of the absence of hints: http://it.toolbox.com/blogs/database-soup/why-postgresql-doesnt-have-query-hints-44121?rss=1 http://it.toolbox.com/blogs/database-soup/why-postgresql-doe.... Also: FIRST_VALUE and LAST_VALUE really need IGNORE NULLS to do proper, high-performance rollforwards for things like time series data and periodic analysis.

gaius 16y ago
OK here is an example off the top of my head. Let's say you have a join between two tables and the query planner wants to do a hash join, because given the statistics on all the tables, that makes perfect sense. Now let's say that you know that you only actually want the first 10 rows. Well a hash join is cheap per row, but expensive to set up. Nested loops are expensive per row, but cheap to set up. So, maybe, actually,as a DBA and a developer I do know what I want, and I can communicate my intent to the planner, and then let it decide. Anyway, I'd be curious to know what flags the Postgres guys pass into GCC when compiling it. Or do they know better than it's "planner"?
- wulczer 16y ago
  To answer your question about getting the first 10 rows, you can use the LIMIT clause, which will influence the planner's decisions. Or you can declare a cursor for your query and only fetch the first 10 rows. As for GCC, it's usually up to the packagers, but the source tree after running ./configure gets compiled with: -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv
  - gaius 16y ago
    That's kinda my point. Aren't those "hints" to the compiler?
    - wulczer 16y ago
      Not in the sense that query hints are "hints" to the planner. -O2 means "take more time compiling but produce faster code". This hint will always be valid and useful, regardless of how the codebase changes. Query planner hints can become obsolete as the data distribution changes. -fwrapv and -fno-strict-aliasing are correctness settings -Wxxx are just help for the developers

by ibejoeb 16y ago

gaius 16y ago

OK here is an example off the top of my head. Let's say you have a join between two tables and the query planner wants to do a hash join, because given the statistics on all the tables, that makes perfect sense. Now let's say that you know that you only actually want the first 10 rows. Well a hash join is cheap per row, but expensive to set up. Nested loops are expensive per row, but cheap to set up. So, maybe, actually,as a DBA and a developer I do know what I want, and I can communicate my intent to the planner, and then let it decide. Anyway, I'd be curious to know what flags the Postgres guys pass into GCC when compiling it. Or do they know better than it's "planner"?

wulczer 16y ago
To answer your question about getting the first 10 rows, you can use the LIMIT clause, which will influence the planner's decisions. Or you can declare a cursor for your query and only fetch the first 10 rows. As for GCC, it's usually up to the packagers, but the source tree after running ./configure gets compiled with: -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv
- gaius 16y ago
  That's kinda my point. Aren't those "hints" to the compiler?
  - wulczer 16y ago
    Not in the sense that query hints are "hints" to the planner. -O2 means "take more time compiling but produce faster code". This hint will always be valid and useful, regardless of how the codebase changes. Query planner hints can become obsolete as the data distribution changes. -fwrapv and -fno-strict-aliasing are correctness settings -Wxxx are just help for the developers