Обсуждение: weird EXPLAIN
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
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.