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

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

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Planner not using column limit specified for one column for another column equal to first
Date: 2010-04-16 08:02:06
Message-ID: m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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;

(plan2.txt)
Total runtime: 27.547 ms

I've thought and someone in this list've told me that this should be done
automatically. But I have pretty recent server:
PostgreSQL 8.4.2 on amd64-portbld-freebsd8.0, compiled by GCC cc (GCC) 4.2.1
20070719  [FreeBSD], 64-bit
and it still do not work

Do I misunderstand something or this feature don't work in such a query?

Best regards, Vitalii Tymchyshyn

Attachment: plan2.txt
Description: text/plain (1.7 KB)
Attachment: plan1.txt
Description: text/plain (1.6 KB)

Responses

pgsql-performance by date

Next:From: Hannu KrosingDate: 2010-04-16 08:25:25
Subject: Re: Planner not using column limit specified for one column for another column equal to first
Previous:From: Dave CrookeDate: 2010-04-15 23:39:37
Subject: SOLVED: Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

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