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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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
    
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 Dec 30 '15 at 2:55

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.