I have question about query planner in PostgreSQL. I know, that this planner is taking data from pg_statistics, but... Can anyone tell me on what basis this planner is taking those informations to make efficient queries? How is it using those statistics? Is there any better description of this whole process, than in the documentation?

I'm fully aware that my question might not be understandable, but I really don't know how to explain my problem easier. If you have questions, please ask.


3 Answers 3


The most detailed discussion of how statistics are used is the Row Estimation Examples section of the documentation. Ultimately all information about how a query might be executed is turned into a serious of costs via the various Cost Constants. So if a table is 1000 pages in size, and the statistics suggest 10% of it will be touched at random by a proposed query, that's 100 pages * 4.0 (random_page_cost) = 400 cost units for pulling in the data; then other constants are used to determine things like processing costs on the data in those pages.

The query optimizer tries various ways of obtaining and combining individual components: different join types, different ways to access the table data, etc. It iterates through the possible plans from those combinations, then picks the one that has the cheapest total cost to execute.

I wrote just over 50 pages on this subject for my book PostgreSQL 9.0 High Performance, which has the longest discussion of query execution available right now. There's isn't too much there on how statistics are used beyond what's shown in the documentation though. Most of it covers all of the various query plan node elements you might run into.

  • 1
    +1 for PostgresQL High Performance (I just cannot do +10 ;). I use PostgreSQL 8.4 and 9.0 for several projects at work. Your book is really a great source of information and helped me a lot.
    – tscho
    Dec 20, 2011 at 0:49
  • Also +1 for PostgreSQL High Performance. Great book, thanks!
    – j.p.
    Dec 20, 2011 at 23:20

There is a nice and very readable readme.txt somewhere in the src/backend/{planner,optimiser} directory, which discusses the transformations and heuristics used by the planner nicely. Dont expect to understand it after a single read. Read it again. And again. Highly recommended.

UPDATE: it is : ./src/backend/optimizer/README

  • I can't find src/backend folder. Where it can be? I'm Windows user.
    – Krzysztof Trzos
    Dec 19, 2011 at 23:55
  • 4
    @wildplasser: that is a very stupid remark regarding folders and women! And besides: it's perfectyl valid to call a "directory" a "folder"
    – a_horse_with_no_name
    Dec 20, 2011 at 0:06
  • No it is not, IMHO. A folder is a graphic representation of something that actually resides on the disk (or on a network disk). Calling it a folder (or map, which might be even worse ...) is a case of mixing metaphores, IMHO.
    – wildplasser
    Dec 20, 2011 at 0:09
  • A "directory" is a metaphor just as well. Most everything is metaphors in the field of computing. Words of the material world have been adapted for new uses. More importantly, sexist remarks are not welcome.
    – Erwin Brandstetter
    Dec 20, 2011 at 11:30
  • But given the fact that a metaphor ("directory") already exists, there is no need to introduce new ones. Introducing new names for old things is only there to confuse the innocent. And there is nothing sexist about being confused or innocent (or female ;-).
    – wildplasser
    Dec 20, 2011 at 11:51

If you really want to know that, well... you can find it here http://www.pgcon.org/2011/schedule/events/350.en.html and you can read PostgreSQL sources.

On the other hand, you really don't need to know that for using PostgreSQL or any other database efficiently. The only thing you need to know is that you the statistics should be fresh.

  • I need it for my college work :)
    – Krzysztof Trzos
    Dec 19, 2011 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.