weird EXPLAIN

On Mon, 2002-06-17 at 16:04, Varun Kacholia wrote:

hi ,
I have just migrated to PostgreSQL...and found the following wierd :

suryadb=# explain select * from dbwin where id in (select id from wdwin
where word='bacd');
NOTICE: QUERY PLAN:
Seq Scan on dbwin (cost=0.00..8158.20 rows=1000 width=76)
^^^^^^^^^^^^^^^^^^
SubPlan
-> Materialize (cost=8.14..8.14 rows=10 width=4)
-> Index Scan using wdkwin on wdwin (cost=0.00..8.14 rows=10 width=4)

i wanted to know why a sequential scan is done on the field id
while it is a primary key in the table dbwin.this can be clearly
confirmed from the query below :

suryadb=# explain select * from dbwin where id=1234;
NOTICE: QUERY PLAN:
Index Scan using dbwin_pkey on dbwin (cost=0.00..8.14 rows=10 width=76)

why does it do a sequential scan for the query above??

If the table is small, the planner figures (usually correctly) that it
is quicker to load it all in memory and do an in-memory scan, rather
than loading the index into memory, getting the row from the index, then
loading the row from disk (2 disc reads to 1)

--

Tom Jenkins
Development InfoStructure
http://www.devis.com

On Tue, 18 Jun 2002, Varun Kacholia wrote:

hi ,
I have just migrated to PostgreSQL...and found the following wierd :

suryadb=# explain select * from dbwin where id in (select id from wdwin
where word='bacd');
NOTICE: QUERY PLAN:
Seq Scan on dbwin (cost=0.00..8158.20 rows=1000 width=76)
^^^^^^^^^^^^^^^^^^
SubPlan
-> Materialize (cost=8.14..8.14 rows=10 width=4)
-> Index Scan using wdkwin on wdwin (cost=0.00..8.14 rows=10 width=4)

Although I'm not sure it'd help this case , it also looks like you
haven't used VACUUM ANALYZE on the tables since the above looks an awful
lot like the defaults.