From: Віталій Тимчишин <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner not using column limit specified for one column for another column equal to first
Date: Fri, 16 Apr 2010 15:59:50 +0300
Message-ID: <k2y331e40661004160559zb22b8ac2i3d1909c0c8ff0704@mail.gmail.com> (raw)
In-Reply-To: <1271406325.27474.24.camel@hvost>
References: <m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com>
<1271406325.27474.24.camel@hvost>
16 квітня 2010 р. 11:25 Hannu Krosing <hannu@2ndquadrant.com> написав:
> 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;
>
And it's still fast (see plan in another mail), while "inner join" variant
of original query is still slow.
>
>
> 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
>
> Actually no,
select id > 50000000, count(*) from company group by 1
f,1096042
t,5725630
I don't know why the planner wishes to perform few merges of 1000 to a
million of records (and the merges is the thing that takes time) instead of
taking a 1000 of records from main table and then doing a nested loop. And
it must read all the records that DO NOT match the criteria for secondary
tables before getting to correct records if it do not filter secondary
tables with index on retrieve.
set enable_mergejoin=false helps original query, but this is another problem
and first solution is simpler and can be used by planner automatically,
while second requires rethinking/rewrite of LIMIT estimation logic
(Plan of nested loop attached)
Attachments:nestplan.txt (text/plain, 1.4K) download
Limit (cost=0.00..2369.36 rows=1000 width=1209) (actual time=0.179..41.155 rows=1000 loops=1)
-> Nested Loop (cost=0.00..15727940.41 rows=6638046 width=1209) (actual time=0.174..38.312 rows=1000 loops=1)
-> Nested Loop (cost=0.00..11165483.75 rows=6701224 width=1141) (actual time=0.134..26.421 rows=1000 loops=1)
-> Nested Loop (cost=0.00..5763844.03 rows=6765004 width=639) (actual time=0.066..14.389 rows=1000 loops=1)
-> Index Scan using pk_comp_m on company this_ (cost=0.00..1152936.77 rows=6886598 width=623) (actual time=0.038..1.908 rows=1054 loops=1)
Index Cond: (id > 50000000)
-> Index Scan using company_tag_company_id_idx on company_tag this_1_ (cost=0.00..0.66 rows=1 width=16) (actual time=0.004..0.006 rows=1 loops=1054)
Index Cond: ((this_1_.company_id)::bigint = this_.id)
Filter: ((this_1_.tag_id)::bigint = 7)
-> Index Scan using cws_company_descr_unique on company_descr ces3_ (cost=0.00..0.79 rows=1 width=502) (actual time=0.004..0.006 rows=1 loops=1000)
Index Cond: ((ces3_.company_id)::bigint = this_.id)
-> Index Scan using pk_comp_ms on company_measures companymea2_ (cost=0.00..0.67 rows=1 width=68) (actual time=0.004..0.006 rows=1 loops=1000)
Index Cond: ((companymea2_.company_id)::bigint = this_.id)
Total runtime: 42.940 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: <k2y331e40661004160559zb22b8ac2i3d1909c0c8ff0704@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