3

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

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

But how does the RDBMS determine which rows must be taken. For example, database calculated that the current query need 1000 entities. But which exactly? How is it determined?

Improve this question

1 Answer 1

Reset to default
2

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

Improve this answer
5
  • 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".
    – Jazi
    Commented Jan 23, 2012 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
    Commented Jan 23, 2012 at 19:18
  • So where exacly I can find how is this "which" determined?
    – Jazi
    Commented Jan 23, 2012 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
    Commented Jan 27, 2012 at 13:46
  • when it comes to databases and result sets, which is not really well defined. This is because records are mostly random (and random is a good thing) because the engine is compiling and returning data as fast as it can. We use order by to have an ordered result set, but that set is a snapshot and not realtime; if an INSERT is performed right after you get your results from your SELECT, the new rows aren't in your query. The database knows little about how you intend to use the database. Data warehouses are better designed to store data in a more consistent manner.
    – vol7ron
    Commented Dec 30, 2015 at 2:55

Your Answer

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

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