2010/7/22 AI Rumman <rummandba@xxxxxxxxx>
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
