On Wed, Nov 16, 2005 at 03:33:10PM +0100, Thies C. Arntzen wrote:
> my question is more in the line of
>
> http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php
>
> whereby my special case is all about beeing able to provide an
> [possible inaccuate] count for a query if possible: my understanding
> is that would be the case if the "where clase" and the "order by"
> clause have been satisfied from the indices and the only step left is
> to validate the records in the result by reading them from the heap.
The problem is that in the index scan you indicated, it goes no further
through the index than necessary to produce your answer. And it can't
satisfy the where clause from the index (unless those columns are in
your index, you don't say).
The logic (very simplified) basically goes:
1. Get next entry in index
2. Does entry match, if not goto 1
3. Extract matching tuple from heap
4. Check visibility and where clause
5. If not match, goto 1
6. Return this tuple
7. Have we returned 50 rows yet, if not goto 1
8. finish
As you can see, when you get to 8 you have no idea how much of the
index you scanned and no idea how much of the table you scanned. You
really have *no* idea how many more there might be. For example, say
step 1 generated 300 tuples, step 2 passed 200 of them and step 4
passed 50 of those (which it returned). How many tuples will the query
return in the end?
It's your assumption that we actually examine more of the index than
necessary that's wrong.
> and -again- i'm not asking for a new feature but i'd like to play
> with it and am asking for hackers advice;-)
Get the result from EXPLAIN, it's about as good as any other estimate we
can produce...
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
