Skip site navigation (1) Skip section navigation (2)

BUG #1427: Seq scan for SELECT 1 FROM p WHERE aid=133 LIMIT 1

From: "Alexander Kirpa" <postgres(at)bilteks(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1427: Seq scan for SELECT 1 FROM p WHERE aid=133 LIMIT 1
Date: 2005-01-21 01:24:11
Message-ID: 200501210124.j0L1OBtZ013742@developer.pgadmin.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1427
Logged by:          Alexander Kirpa
Email address:      postgres(at)bilteks(dot)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

pgsql-bugs by date

Next:From: Hendrik MuellerDate: 2005-01-21 10:35:59
Subject: BUG #1428: SHGetSpecialFolderPath not found in SHELL32.dll
Previous:From: Tom LaneDate: 2005-01-21 00:20:28
Subject: Re: BUG #1410: Hibernate PerformanceTest is incredibly slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group