From: | Hannu Krosing <hannu(at)2ndquadrant(dot)com> |
---|---|
To: | Віталій Тимчишин <tivv00(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Planner not using column limit specified for one column for another column equal to first |
Date: | 2010-04-16 08:25:25 |
Message-ID: | 1271406325.27474.24.camel@hvost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, 2010-04-16 at 11:02 +0300, Віталій Тимчишин wrote:
> Hello.
>
>
> I have a query that performs very poor because there is a limit on
> join column that is not applied to other columns:
>
>
> select * from company this_ left outer join company_tag this_1_ on
> this_.id=this_1_.company_id left outer join company_measures
> companymea2_ on this_.id=companymea2_.company_id left outer join
> company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id
> = 7 and this_.id>50000000
> and this_1_.company_id>50000000
> order by this_.id asc limit 1000;
>
>
> (plan1.txt)
> Total runtime: 7794.692 ms
>
>
> At the same time if I apply the limit (>50000000) to other columns in
> query itself it works like a charm:
>
>
> select * from company this_ left outer join company_tag this_1_ on
> this_.id=this_1_.company_id left outer join company_measures
> companymea2_ on this_.id=companymea2_.company_id left outer join
> company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id
> = 7 and this_.id>50000000
> and this_1_.company_id>50000000
> and companymea2_.company_id>50000000 and ces3_.company_id>50000000
> order by this_.id asc limit 1000;
The queries are not the same.
2nd variant will not return the rows where there are no matching rows
inthis_1_ , companymea2_ or ces3_.company_id
A query equivalent to first one would be:
select * from company this_
left outer join company_tag this_1_
on (this_.id=this_1_.company_id
and this_1_.company_id>50000000)
left outer join company_measures companymea2_
on (this_.id=companymea2_.company_id
and companymea2_.company_id>50000000)
left outer join company_descr ces3_
on (this_.id=ces3_.company_id
and ces3_.company_id>50000000)
where this_1_.tag_id = 7
and this_.id>50000000
order by this_.id asc
limit 1000;
I'm not sure that planner considers the above form of plan rewrite, nor
that it would make much sense to do so unless there was a really small
number of rows where x_.company_id>50000000
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
From | Date | Subject | |
---|---|---|---|
Next Message | Yeb Havinga | 2010-04-16 08:31:06 | Re: Planner not using column limit specified for one column for another column equal to first |
Previous Message | Віталій Тимчишин | 2010-04-16 08:02:06 | Planner not using column limit specified for one column for another column equal to first |