Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from meta.dba.stackexchange.com Dec 20 '11 at 19:22

This question came from our discussion, support, and feature requests site for database professionals who wish to improve their database skills and learn from others in the community.

1  
You might also want to take a look at Server Configuration->Query Planning. –  tscho Dec 20 '11 at 0:42

3 Answers 3

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.

share|improve this answer
    
I need it for my college work :) –  Krzysztof Trzos Dec 19 '11 at 23:35

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

share|improve this answer
    
I can't find src/backend folder. Where it can be? I'm Windows user. –  Krzysztof Trzos Dec 19 '11 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 '11 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 '11 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 '11 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 '11 at 11:51

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.

share|improve this answer
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 '11 at 0:49
    
Also +1 for PostgreSQL High Performance. Great book, thanks! –  j.p. Dec 20 '11 at 23:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.