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

Re: Planner not using column limit specified for one column for another column equal to first

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 (view raw or flat)
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



In response to

Responses

pgsql-performance by date

Next:From: Yeb HavingaDate: 2010-04-16 08:31:06
Subject: Re: Planner not using column limit specified for one column for another column equal to first
Previous:From: Віталій ТимчишинDate: 2010-04-16 08:02:06
Subject: Planner not using column limit specified for one column for another column equal to first

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