On Jun 1, 2007, at 12:24 , Tyler Durden wrote:
> On 6/1/07, Joshua D. Drake <jd@commandprompt.com> wrote:
>
>> Nothing. You have to scan the table because you aren't giving
>> postgresql
>> anything to use the index by.
> # explain ANALYZE select id from table_name where id>200000;
>
> QUERY PLAN
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> Index Scan using table_name_pkey on table_name (cost=0.00..2618.96
> rows=68960 width=4) (actual time=220.543..1479.495 rows=66730 loops=1)
> Index Cond: (id > 200000)
> Total runtime: 1504.839 ms
> (3 rows)
>
> dun=# explain ANALYZE select id from table_name where id>10;
> QUERY PLAN
> ----------------------------------------------------------------------
> ------------------------------------------------
> Seq Scan on table_name (cost=0.00..9268.12 rows=266703 width=4)
> (actual time=107.935..2733.592 rows=266720 loops=1)
> Filter: (id > 10)
> Total runtime: 2833.744 ms
> (3 rows)
>
>
> It uses Index Scan for id>200000 and Seq Scan for id>10?!
[Please don't top-post. It makes discussions harder to follow]
Because the planner estimates that it will be faster for it to scan
the entire table than to use the index in the latter case. Note that
only about 70,000 rows need to be visited for id > 200000, while
nearly 270,000 rows need to be visited when id > 10.
Michael Glaesemann
grzm seespotcode net