Tell me more ×
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.
up vote 2 down vote favorite

I'm working on college work about how query planner uses statistics and makes the most optimal query.

I have read the 57.1. Row Estimation Examples article and I know how's the PostgreSQL calculates the amount of entities taken from the database.

My question about this is, how database specifies which row's must be taken. For example, database calculated that the current query need 1000 entities. But which exactly? How is it determined?

Thank You in advance.

share|improve this question

1 Answer

up vote 1 down vote

You may have missed that spot in the chapter of the manual you quote:

Note also that since ANALYZE uses random sampling while producing statistics, the results will change slightly after any new ANALYZE.

Emphasis mine.

If you actually mean "how many" rows in your question: the query planner has selectivity estimators for certain operators in the conditions. In combination with the statistics gathered the planner estimates the number of rows for every step.

Details of row estimation can be found the chapter of the manual you already link to in your question.

Estimations can be way off if the data distribution is very uneven within a table. That's why raising the setting for default_statistics-target (and running ANALYZE afterwards) may help in such cases.

Some special operators have special selectivity estimators. Like text search. Details in the source code in:
src/backend/tsearch/ts_selfuncs.c

share|improve this answer
Yes, but still we have only a number of entities, that database will take. Erwin, I have read that details and I have mentioned that in my question :). I know "how many", but I don't know "which". – Krzysztof Trzos Jan 23 '12 at 19:04
@KrzysztofTrzos: There is no exact "which" in the estimation. That's only determined in the execution of the query at a much higher cost and pretty much at the core of the database engine. – Erwin Brandstetter Jan 23 '12 at 19:18
So where exacly I can find how is this "which" determined? – Krzysztof Trzos Jan 23 '12 at 19:49
the query plan shows how the rows will be found. but before the query plan is produced, of course the database doesn't know which rows will be found, it only has an estimate of how many. – araqnid Jan 27 '12 at 13:46

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.