Re: Planner not using column limit specified for one column for another column equal to first - Mailing list pgsql-performance

From Hannu Krosing
Subject Re: Planner not using column limit specified for one column for another column equal to first
Date
Msg-id 1271406325.27474.24.camel@hvost
Whole thread Raw
In response to Planner not using column limit specified for one column for another column equal to first  (Віталій Тимчишин <tivv00@gmail.com>)
List 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



pgsql-performance by date:

Previous
From: norn
Date:
Subject: Re: significant slow down with various LIMIT
Next
From: Dave Crooke
Date:
Subject: Re: [JDBC] SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set