My main message is that I can see this in many queries and many times. But OK, I can present exact example.
2008/11/5 Jeff Davis <pgsql@xxxxxxxxxxx>
Server version 8.3.3
I have autovacuum, but for this example I did vacuum analyze of the whole DB.
The real-life query (autogenerated) looks like the next:
select t0.id as pk1,t1.id as pk2 ,t0.run_id as f1_run_id,t1.run_id as f2_run_id
from tmpv_unproc_null_production_company_dup_cons_company as t0, (select * from production.company where run_id in (select id from production.run where name='test')) as t1
where
t0.name = t1.name
or
(t0.name,t1.name) in (select s1.name, s2.name from atom_match inner join atoms_string s1 on atom_match.atom1_id = s1.id inner join atoms_string s2 on atom_match.atom2_id = s2.id where s1.atom_type_id = -1 and match_function_id = 2)
with tmpv_unproc_null_production_company_dup_cons_company:
create temporary view tmpv_unproc_null_production_company_dup_cons_company as select * from production.company where 1=1 and status='unprocessed' and run_id in (select id from production.run where name='test')
On Wed, 2008-11-05 at 13:12 +0200, Віталій Тимчишин wrote:What version are you using?
> For a long time already I can see very poor OR performance in
> postgres.
> If one have query like "select something from table where condition1
> or condition2" it may take ages to execute while
> "select something from table where condition1" and "select something
> from table where condition2" are executed very fast and
> "select something from table where condition1 and not condition2 union
> all select something from table where condition2" gives required
> results fast
>
Server version 8.3.3
Have you run "VACUUM ANALYZE"?
I have autovacuum, but for this example I did vacuum analyze of the whole DB.
The real-life query (autogenerated) looks like the next:
select t0.id as pk1,t1.id as pk2 ,t0.run_id as f1_run_id,t1.run_id as f2_run_id
from tmpv_unproc_null_production_company_dup_cons_company as t0, (select * from production.company where run_id in (select id from production.run where name='test')) as t1
where
t0.name = t1.name
or
(t0.name,t1.name) in (select s1.name, s2.name from atom_match inner join atoms_string s1 on atom_match.atom1_id = s1.id inner join atoms_string s2 on atom_match.atom2_id = s2.id where s1.atom_type_id = -1 and match_function_id = 2)
with tmpv_unproc_null_production_company_dup_cons_company:
create temporary view tmpv_unproc_null_production_company_dup_cons_company as select * from production.company where 1=1 and status='unprocessed' and run_id in (select id from production.run where name='test')
Next, do:
EXPLAIN ANALYZE select something from table where condition1 or
condition2;
without analyze is in OR-plan.txt
Also plans for only condition1, only condition2 and union is attached
Also plans for only condition1, only condition2 and union is attached
"Nested Loop (cost=4588.13..960900482668.95 rows=1386158171 width=32)" " Join Filter: (((production.company.name)::text = (production.company.name)::text) OR (subplan))" " -> Hash IN Join (cost=1.56..73814.22 rows=52648 width=30)" " Hash Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Seq Scan on company (cost=0.00..64599.29 rows=2316503 width=30)" " Filter: ((status)::text = 'unprocessed'::text)" " -> Hash (cost=1.55..1.55 rows=1 width=8)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8)" " Filter: ((name)::text = 'test'::text)" " -> Nested Loop (cost=1183.27..39219.67 rows=52648 width=30)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38435.51 rows=62608 width=30)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" " SubPlan" " -> Materialize (cost=3403.29..4005.74 rows=35745 width=28)" " -> Hash Join (cost=928.57..3122.55 rows=35745 width=28)" " Hash Cond: ((atom_match.atom1_id)::integer = s1.id)" " -> Hash Join (cost=445.80..1880.19 rows=35745 width=18)" " Hash Cond: ((atom_match.atom2_id)::integer = s2.id)" " -> Seq Scan on atom_match (cost=0.00..674.81 rows=35745 width=8)" " Filter: ((match_function_id)::integer = 2)" " -> Hash (cost=260.91..260.91 rows=14791 width=18)" " -> Seq Scan on atoms_string s2 (cost=0.00..260.91 rows=14791 width=18)" " -> Hash (cost=297.89..297.89 rows=14791 width=18)" " -> Seq Scan on atoms_string s1 (cost=0.00..297.89 rows=14791 width=18)" " Filter: ((atom_type_id)::integer = (-1))"
"Merge Join (cost=89373.23..97526.15 rows=525975 width=32) (actual time=276.869..523.669 rows=34749 loops=1)" " Merge Cond: ((production.company.name)::text = (production.company.name)::text)" " -> Sort (cost=44764.87..44896.49 rows=52648 width=30) (actual time=120.036..144.925 rows=15507 loops=1)" " Sort Key: production.company.name" " Sort Method: external merge Disk: 704kB" " -> Nested Loop (cost=1183.27..39376.19 rows=52648 width=30) (actual time=1.898..72.693 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38592.03 rows=62608 width=30) (actual time=1.873..29.251 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " Filter: ((production.company.status)::text = 'unprocessed'::text)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.784..1.784 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Materialize (cost=44608.35..45266.45 rows=52648 width=30) (actual time=156.822..252.497 rows=34749 loops=1)" " -> Sort (cost=44608.35..44739.97 rows=52648 width=30) (actual time=156.817..182.079 rows=15507 loops=1)" " Sort Key: production.company.name" " Sort Method: external merge Disk: 704kB" " -> Nested Loop (cost=1183.27..39219.67 rows=52648 width=30) (actual time=1.883..76.580 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.023..0.026 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38435.51 rows=62608 width=30) (actual time=1.844..27.476 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.757..1.757 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" "Total runtime: 572.370 ms"
"Hash Join (cost=49771.10..88028.96 rows=2403 width=32) (actual time=1052.358..1256.355 rows=53495 loops=1)" " Hash Cond: ((production.company.name)::text = (s2.name)::text)" " -> Nested Loop (cost=1183.27..39219.67 rows=52648 width=30) (actual time=1.903..71.109 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.029..0.032 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.019..0.021 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38435.51 rows=62608 width=30) (actual time=1.867..26.079 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.780..1.780 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Hash (cost=48471.98..48471.98 rows=9268 width=44) (actual time=1050.273..1050.273 rows=45091 loops=1)" " -> Hash Join (cost=8826.52..48471.98 rows=9268 width=44) (actual time=761.377..959.409 rows=45091 loops=1)" " Hash Cond: ((production.company.name)::text = ((s1.name)::text))" " -> Nested Loop (cost=1183.27..39376.19 rows=52648 width=30) (actual time=1.767..71.797 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.015..0.018 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38592.03 rows=62608 width=30) (actual time=1.746..29.069 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " Filter: ((production.company.status)::text = 'unprocessed'::text)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.662..1.662 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Hash (cost=6951.43..6951.43 rows=35745 width=28) (actual time=759.420..759.420 rows=35745 loops=1)" " -> Unique (cost=6683.34..6951.43 rows=35745 width=28) (actual time=524.617..693.365 rows=35745 loops=1)" " -> Sort (cost=6683.34..6772.71 rows=35745 width=28) (actual time=524.614..593.649 rows=35745 loops=1)" " Sort Key: ((s1.name)::text), ((s2.name)::text)" " Sort Method: external merge Disk: 2064kB" " -> Hash Join (cost=928.57..3122.55 rows=35745 width=28) (actual time=91.540..380.449 rows=35745 loops=1)" " Hash Cond: ((atom_match.atom1_id)::integer = s1.id)" " -> Hash Join (cost=445.80..1880.19 rows=35745 width=18) (actual time=45.322..215.477 rows=35745 loops=1)" " Hash Cond: ((atom_match.atom2_id)::integer = s2.id)" " -> Seq Scan on atom_match (cost=0.00..674.81 rows=35745 width=8) (actual time=0.006..56.541 rows=35745 loops=1)" " Filter: ((match_function_id)::integer = 2)" " -> Hash (cost=260.91..260.91 rows=14791 width=18) (actual time=45.304..45.304 rows=14791 loops=1)" " -> Seq Scan on atoms_string s2 (cost=0.00..260.91 rows=14791 width=18) (actual time=0.004..21.717 rows=14791 loops=1)" " -> Hash (cost=297.89..297.89 rows=14791 width=18) (actual time=46.203..46.203 rows=14791 loops=1)" " -> Seq Scan on atoms_string s1 (cost=0.00..297.89 rows=14791 width=18) (actual time=0.007..22.026 rows=14791 loops=1)" " Filter: ((atom_type_id)::integer = (-1))" "Total runtime: 1332.827 ms"
"Unique (cost=266348.42..272953.14 rows=528378 width=32) (actual time=2259.262..2662.097 rows=88244 loops=1)" " -> Sort (cost=266348.42..267669.36 rows=528378 width=32) (actual time=2259.258..2417.291 rows=88244 loops=1)" " Sort Key: production.company.id, production.company.id, production.company.run_id, production.company.run_id" " Sort Method: external merge Disk: 4128kB" " -> Append (cost=49771.10..190838.89 rows=528378 width=32) (actual time=1080.083..2024.480 rows=88244 loops=1)" " -> Hash Join (cost=49771.10..88028.96 rows=2403 width=32) (actual time=1080.080..1285.869 rows=53495 loops=1)" " Hash Cond: ((production.company.name)::text = (s2.name)::text)" " -> Nested Loop (cost=1183.27..39219.67 rows=52648 width=30) (actual time=1.949..68.977 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.027..0.029 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38435.51 rows=62608 width=30) (actual time=1.913..25.456 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.818..1.818 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Hash (cost=48471.98..48471.98 rows=9268 width=44) (actual time=1077.956..1077.956 rows=45091 loops=1)" " -> Hash Join (cost=8826.52..48471.98 rows=9268 width=44) (actual time=757.742..987.008 rows=45091 loops=1)" " Hash Cond: ((production.company.name)::text = ((s1.name)::text))" " -> Nested Loop (cost=1183.27..39376.19 rows=52648 width=30) (actual time=1.785..79.869 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.015..0.018 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38592.03 rows=62608 width=30) (actual time=1.763..30.764 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " Filter: ((production.company.status)::text = 'unprocessed'::text)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.679..1.679 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Hash (cost=6951.43..6951.43 rows=35745 width=28) (actual time=755.771..755.771 rows=35745 loops=1)" " -> Unique (cost=6683.34..6951.43 rows=35745 width=28) (actual time=520.778..689.501 rows=35745 loops=1)" " -> Sort (cost=6683.34..6772.71 rows=35745 width=28) (actual time=520.774..589.872 rows=35745 loops=1)" " Sort Key: ((s1.name)::text), ((s2.name)::text)" " Sort Method: external merge Disk: 2064kB" " -> Hash Join (cost=928.57..3122.55 rows=35745 width=28) (actual time=92.883..375.943 rows=35745 loops=1)" " Hash Cond: ((atom_match.atom1_id)::integer = s1.id)" " -> Hash Join (cost=445.80..1880.19 rows=35745 width=18) (actual time=45.651..212.089 rows=35745 loops=1)" " Hash Cond: ((atom_match.atom2_id)::integer = s2.id)" " -> Seq Scan on atom_match (cost=0.00..674.81 rows=35745 width=8) (actual time=0.006..54.235 rows=35745 loops=1)" " Filter: ((match_function_id)::integer = 2)" " -> Hash (cost=260.91..260.91 rows=14791 width=18) (actual time=45.627..45.627 rows=14791 loops=1)" " -> Seq Scan on atoms_string s2 (cost=0.00..260.91 rows=14791 width=18) (actual time=0.005..21.782 rows=14791 loops=1)" " -> Hash (cost=297.89..297.89 rows=14791 width=18) (actual time=47.219..47.219 rows=14791 loops=1)" " -> Seq Scan on atoms_string s1 (cost=0.00..297.89 rows=14791 width=18) (actual time=0.007..22.375 rows=14791 loops=1)" " Filter: ((atom_type_id)::integer = (-1))" " -> Merge Join (cost=89373.23..97526.15 rows=525975 width=32) (actual time=237.562..498.501 rows=34749 loops=1)" " Merge Cond: ((production.company.name)::text = (production.company.name)::text)" " -> Sort (cost=44764.87..44896.49 rows=52648 width=30) (actual time=120.482..147.512 rows=15507 loops=1)" " Sort Key: production.company.name" " Sort Method: external merge Disk: 704kB" " -> Nested Loop (cost=1183.27..39376.19 rows=52648 width=30) (actual time=1.919..72.063 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.034..0.037 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38592.03 rows=62608 width=30) (actual time=1.869..29.107 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " Filter: ((production.company.status)::text = 'unprocessed'::text)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.771..1.771 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Materialize (cost=44608.35..45266.45 rows=52648 width=30) (actual time=117.069..217.735 rows=34749 loops=1)" " -> Sort (cost=44608.35..44739.97 rows=52648 width=30) (actual time=117.062..144.027 rows=15507 loops=1)" " Sort Key: production.company.name" " Sort Method: external merge Disk: 704kB" " -> Nested Loop (cost=1183.27..39219.67 rows=52648 width=30) (actual time=1.887..68.266 rows=15507 loops=1)" " -> HashAggregate (cost=1.55..1.56 rows=1 width=8) (actual time=0.024..0.026 rows=1 loops=1)" " -> Seq Scan on run (cost=0.00..1.55 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)" " Filter: ((name)::text = 'test'::text)" " -> Bitmap Heap Scan on company (cost=1181.72..38435.51 rows=62608 width=30) (actual time=1.851..24.648 rows=15507 loops=1)" " Recheck Cond: ((production.company.run_id)::bigint = production.run.id)" " -> Bitmap Index Scan on comp_run (cost=0.00..1166.07 rows=62608 width=0) (actual time=1.764..1.764 rows=15507 loops=1)" " Index Cond: ((production.company.run_id)::bigint = production.run.id)" "Total runtime: 2797.311 ms"
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
