Joshua Marsh wrote: > > > On 11/14/05, *Steve Wampler* <swampler@xxxxxxxx > <mailto:swampler@xxxxxxxx>> wrote: > > However, even that seems to have a much higher cost than I'd expect: > > lab.devel.configdb=# explain delete from "tmp_table2" where id in > (select id from tmp_table2 where name='obsid' and value = > 'oid080505'); > NOTICE: QUERY PLAN: > > Seq Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 > width=6) > SubPlan > -> Materialize (cost=42674.32..42674.32 rows=38 width=50) > -> Seq Scan on tmp_table2 (cost=0.00..42674.32 > rows=38 width=50) > > > For one reason or the other, the planner things a sequential scan is the > best solution. Try turning off seq_scan before the query and see if it > changes the plan (set enable_seqscan off;). > > I've seen this problem with sub queries and that usually solves it. > Hmmm, not only does it still use sequential scans, it thinks it'll take even longer: set enable_seqscan to off; SET VARIABLE explain delete from "tmp_table2" where id in (select id from tmp_table2 where name='obsid' and value = 'oid080505'); NOTICE: QUERY PLAN: Seq Scan on tmp_table2 (cost=100000000.00..160237039405992.50 rows=800836 width=6) SubPlan -> Materialize (cost=100043604.06..100043604.06 rows=45 width=26) -> Seq Scan on tmp_table2 (cost=100000000.00..100043604.06 rows=45 width=26) EXPLAIN But the advice sounds like it *should* have helped... -- Steve Wampler -- swampler@xxxxxxxx The gods that smiled on your birth are now laughing out loud. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
