From: Віталій Тимчишин <tivv00@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Planner not using column limit specified for one column for another column equal to first
Date: Fri, 16 Apr 2010 11:02:06 +0300
Message-ID: <m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com> (raw)
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
Attachments:plan1.txt (text/plain, 1.6K) download
Limit (cost=497.46..1464.50 rows=1000 width=693) (actual time=7767.721..7793.047 rows=1000 loops=1)
-> Merge Left Join (cost=497.46..5521612.64 rows=5709243 width=693) (actual time=7767.717..7790.274 rows=1000 loops=1)
Merge Cond: (this_.id = (companymea2_.company_id)::bigint)
-> Merge Left Join (cost=404.31..4544508.54 rows=5709243 width=625) (actual time=4211.501..4227.215 rows=1000 loops=1)
Merge Cond: (this_.id = (ces3_.company_id)::bigint)
-> Merge Join (cost=37.87..2435536.00 rows=5709243 width=123) (actual time=0.069..8.584 rows=1000 loops=1)
Merge Cond: (this_.id = (this_1_.company_id)::bigint)
-> Index Scan using pk_comp_m on company this_ (cost=0.00..2139324.53 rows=6886598 width=107) (actual time=0.031..1.876 rows=1054 loops=1)
Index Cond: (id > 50000000)
-> Index Scan using company_tag_company_id_idx on company_tag this_1_ (cost=0.00..207678.85 rows=5709243 width=16) (actual time=0.027..1.757 rows=1000 loops=1)
Index Cond: ((this_1_.company_id)::bigint > 50000000)
Filter: ((this_1_.tag_id)::bigint = 7)
-> Index Scan using company_descrs on company_descr ces3_ (cost=0.00..2073526.89 rows=1996612 width=502) (actual time=0.014..2576.013 rows=1097096 loops=1)
-> Index Scan using pk_comp_ms on company_measures companymea2_ (cost=0.00..889427.81 rows=6821672 width=68) (actual time=0.020..1946.255 rows=1097096 loops=1)
Total runtime: 7794.692 ms
plan2.txt (text/plain, 1.7K) download
Limit (cost=330.57..4888.97 rows=1000 width=693) (actual time=0.125..26.011 rows=1000 loops=1)
-> Merge Join (cost=330.57..5260651.29 rows=1153986 width=693) (actual time=0.122..23.215 rows=1000 loops=1)
Merge Cond: (this_.id = (this_1_.company_id)::bigint)
-> Merge Join (cost=292.71..5023728.99 rows=1391960 width=677) (actual time=0.090..16.615 rows=1054 loops=1)
Merge Cond: ((companymea2_.company_id)::bigint = this_.id)
-> Merge Join (cost=0.00..2846769.89 rows=1685017 width=570) (actual time=0.063..9.534 rows=1054 loops=1)
Merge Cond: ((companymea2_.company_id)::bigint = (ces3_.company_id)::bigint)
-> Index Scan using pk_comp_ms on company_measures companymea2_ (cost=0.00..848312.00 rows=5688885 width=68) (actual time=0.033..1.973 rows=1054 loops=1)
Index Cond: ((company_id)::bigint > 50000000)
-> Index Scan using company_descrs on company_descr ces3_ (cost=0.00..1963172.96 rows=1685017 width=502) (actual time=0.020..2.152 rows=1054 loops=1)
Index Cond: ((ces3_.company_id)::bigint > 50000000)
-> Index Scan using pk_comp_m on company this_ (cost=0.00..2139324.53 rows=6886598 width=107) (actual time=0.022..1.855 rows=1054 loops=1)
Index Cond: (this_.id > 50000000)
-> Index Scan using company_tag_company_id_idx on company_tag this_1_ (cost=0.00..207678.85 rows=5709243 width=16) (actual time=0.025..1.742 rows=1000 loops=1)
Index Cond: ((this_1_.company_id)::bigint > 50000000)
Filter: ((this_1_.tag_id)::bigint = 7)
Total runtime: 27.547 ms
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com
Subject: Re: Planner not using column limit specified for one column for another column equal to first
In-Reply-To: <m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox