Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL OR performance

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: "Jeff Davis" <pgsql(at)j-davis(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL OR performance
Date: 2008-11-06 10:46:47
Message-ID: 331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)j-davis(dot)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

Attachment: union-plan.txt
Description: text/plain (8.5 KB)
Attachment: condition2-plan.txt
Description: text/plain (4.3 KB)
Attachment: condition1-plan.txt
Description: text/plain (2.6 KB)
Attachment: OR-plan.txt
Description: text/plain (2.2 KB)

In response to

Responses

pgsql-performance by date

Next:From: Tino SchwarzeDate: 2008-11-06 10:57:27
Subject: Re: server space increasing very fast but transaction are very low
Previous:From: brahma tiwariDate: 2008-11-06 10:45:02
Subject: server space increasing very fast but transaction are very low

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group