The following bug has been logged online:
Bug reference: 1427
Logged by: Alexander Kirpa
Email address: postgres@bilteks.com
PostgreSQL version: 8.0.0
Operating system: FreeBSD 5.2.1
Description: Seq scan for SELECT 1 FROM p WHERE aid=133 LIMIT 1
Details:
CREATE TABLE p
(
aid int4 NOT NULL,
sid int4 NOT NULL,
CONSTRAINT p_pk PRIMARY KEY (aid, sid)
)
==== In real table much more columns
select count(*) from p;
count
---------
2522477
====
select count(distinct aid) from p;
count
-------
398
====
select count(aid) from p group by aid order by 1 desc limit 1;
count
--------
681154
====
explain analyze select 1 FROM p where aid=63 limit 1;
Limit (cost=0.00..1.13 rows=1 width=0) (actual time=818.526..818.528 rows=1
loops=1)
-> Seq Scan on p (cost=0.00..72085.36 rows=63566 width=0) (actual
time=818.519..818.519 rows=1 loops=1)
Filter: (aid = 63)
Total runtime: 818.612 ms
==== WRONG optimizator way ^^^^^^^^ ====
==== CORRECT optimizator choice vvvvvvvvv ====
explain analyze select 1 FROM p where aid=133 limit 1;
Limit (cost=0.00..2.00 rows=1 width=0) (actual time=0.116..0.117 rows=1
loops=1)
-> Index Scan using p_pk on p (cost=0.00..4082.03 rows=2040 width=0)
(actual time=0.112..0.112 rows=1 loops=1)
Index Cond: (aid = 133)
Total runtime: 0.185 ms
Using
set enable_seqscan=on/off
or create simple index
CREATE INDEX aid_ix ON p USING btree (aid);
"solve" problem.
I think that in similar case (result is CONSTANT and/or
not depend from returned rows)
don't need calculate time of scaning all rows
in index diapason,
need calculate time scaning only ONE row and
as result we avoid
sequence scaning full table (or until seek first row).
Best regards,
Alexander Kirpa