Comment by gaius | Hacker News Reader

Comment by gaius | Hacker News Reader

Parent Full thread

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"?

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

That's kinda my point. Aren't those "hints" to the compiler?

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

Reply on news.ycombinator.com