Re: PostgreSQL OR performance
| От | Віталій Тимчишин |
|---|---|
| Тема | Re: PostgreSQL OR performance |
| Дата | |
| Msg-id | 331e40660811060246m51df553egd4e2ba91f08d1434@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: PostgreSQL OR performance (Jeff Davis <pgsql@j-davis.com>) |
| Ответы |
Re: PostgreSQL OR performance
|
| Список | 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@j-davis.com>
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
Вложения
В списке pgsql-performance по дате отправления: