hi,
I cannot get what causes the difference in the execution of these 2 commands
1. SELECT * FROM dbmedia WHERE ID IN ((SELECT id FROM wdmedia WHERE word = 'whatever') ) LIMIT 20;
2. SELECT * FROM dbmedia WHERE ID IN (1234,2345,1242,1256,1245,1567,2222,22,345,234,567,456,35,56) ;
where ID is the primary key.
First let me tell that i have done all vacuum's ..analyze,full et all.
The explain output should make it clear that for the former a "sequential"
scan is done and for the later an "index" scan is done.Also the time
take indicates that a sequential scan(atleast not index scan) is done
on the former.
-------------------------------------------------------------------------
explain SELECT * FROM dbmedia WHERE ID IN ((SELECT id FROM wdmedia WHERE word = 'whatever') ) LIMIT 20;
NOTICE: QUERY PLAN:
Limit (cost=0.00..100544.53 rows=20 width=76)
-> Seq Scan on dbmedia (cost=0.00..507161673.46 rows=100883 width=76)
^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ GAWD!!
SubPlan
-> Materialize (cost=5027.19..5027.19 rows=2575 width=4)
-> Index Scan using wdkmedia on wdmedia (cost=0.00..5027.19 rows=2575 width=4)
----------------------------------------------------------------------------
explain SELECT * FROM dbmedia WHERE ID IN (1234,2345,1242,1256,1245,1567,2222,22,345,234,567,456,35,56) ;
NOTICE: QUERY PLAN:
Index Scan using dbmedia_pkey, dbmedia_pkey, dbmedia_pkey, dbmedia_pkey, dbmedia_pkey, dbmedia_pkey, dbmedia_pkey,
dbmedia_pkey,dbmedia_pkey, dbmedia_pkey, dbmedia_pkey, dbmedia_pkey, dbmedia_pkey, dbmedia_pkey on dbmedia
(cost=0.00..59.40rows=1 width=76)
--------------------------------------------------------------------------
observe the index scan..
OK , for those ppl who might be feeling that the o/p of the nested query in the
former case might be a significant portion (and so the db does a sq scan)
let me say that the result set of sub-query (SELECT id FROM wdmedia WHERE word = 'whatever') is of length 1,000 while
thetable dbmedia is of length
100,000 and if a db selects to do a seq scan due to this then ... i can
say no more.
postgresql developers/gurus please help.
--
------
Varun
Printer not ready. Do you have a pen?
----- End forwarded message -----
--
------
Varun
Printer not ready. Do you have a pen?
