pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Віталій Тимчишин <tivv00@gmail.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL OR performance
Date: Thu, 6 Nov 2008 12:46:47 +0200
Message-ID: <331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com> (raw )
In-Reply-To: <1225902700.1432.53.camel@jdavis >
References: <331e40660811050312u552cab53ifa88e493e1c4f7ac@mail.gmail.com >
<1225902700.1432.53.camel@jdavis >
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@j-davis.com>
> On Wed, 2008-11-05 at 13:12 +0200, Віталій Тимчишин wrote:
> > 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
> >
>
> What version are you using?
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
Attachments:
OR-plan.txt (text/plain, 2.2K) download
"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))"
condition1-plan.txt (text/plain, 2.6K) download
"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"
condition2-plan.txt (text/plain, 4.3K) download
"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"
union-plan.txt (text/plain, 8.5K) download
"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"
view thread (16+ messages) latest in thread
Message-ID: <331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com>
Permalink: ../331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com/
Also on: postgresql.org/message-id/331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, pgsql@j-davis.com
Subject: Re: PostgreSQL OR performance
In-Reply-To: <331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox