Re: why index is not working in < operation?

2010/7/22 AI Rumman <rummandba(at)gmail(dot)com>

> I have a table.
>
> \d email_track
> Table "public.email_track"
> Column | Type | Modifiers
> --------+---------+--------------------
> crmid | integer | not null default 0
> mailid | integer | not null default 0
> count | integer |
> Indexes:
> "email_track_pkey" PRIMARY KEY, btree (crmid, mailid) CLUSTER
> "email_track_count_idx" btree (count)
>
>
> explain analyze select * from email_track where count > 10 ;
> QUERY
> PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on email_track (cost=12.79..518.05 rows=1941 width=12)
> (actual time=0.430..3.047 rows=1743 loops=1)
> Recheck Cond: (count > 10)
> -> Bitmap Index Scan on email_track_count_idx (cost=0.00..12.79
> rows=1941 width=0) (actual time=0.330..0.330 rows=1743 loops=1)
> Index Cond: (count > 10)
> Total runtime: 4.702 ms
> (5 rows)
>
> explain analyze select * from email_track where count < 10000 ;
> QUERY
> PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on email_track (cost=0.00..1591.65 rows=88851 width=12) (actual
> time=0.011..118.499 rows=88852 loops=1)
> Filter: (count < 10000)
> Total runtime: 201.206 ms
> (3 rows)
>
> I don't know why index scan is not working for count < 10000 operation.
> Any idea please.
>

Database knows, due to table statistics, that the query ">10" would return
small (1941) number of rows, while query "<10000" would return big
(88851) number of rows. The "small" and "big" is quite relative, but the
result is that the database knows, that it would be faster not to use index,
if the number of returning rows is big.

regards
Szymon Guz