Re: 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