Postgres Pro
Downloads
Home   >   mailing lists

Thread: weird EXPLAIN

weird EXPLAIN

From
Varun Kacholia
Date:
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??
Also i would be happy if someone could explain me the numbers in the
cost thing.. i read the docs....but it is kinda confusing and incomplete.

--
------
Varun
Intel Inside - Idiot outside

Re: weird EXPLAIN

From
Tom Jenkins
Date:
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

Re: weird EXPLAIN

From
Stephan Szabo
Date:
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.