pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanner not using column limit specified for one column for another column equal to first
12+ messages / 5 participants
[nested] [flat]
* Planner not using column limit specified for one column for another column equal to first
@ 2010-04-16 08:02 Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 08:25 ` Re: Planner not using column limit specified for one column for another column equal to first Hannu Krosing <hannu@2ndquadrant.com>
2010-04-16 08:31 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 14:19 ` Re: Planner not using column limit specified for one column for another column equal to first Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 12+ messages in thread
From: Віталій Тимчишин @ 2010-04-16 08:02 UTC (permalink / raw)
To: 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
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
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
@ 2010-04-16 08:25 ` Hannu Krosing <hannu@2ndquadrant.com>
2010-04-16 12:59 ` Re: Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Hannu Krosing @ 2010-04-16 08:25 UTC (permalink / raw)
To: Віталій Тимчишин <tivv00@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 08:25 ` Re: Planner not using column limit specified for one column for another column equal to first Hannu Krosing <hannu@2ndquadrant.com>
@ 2010-04-16 12:59 ` Віталій Тимчишин <tivv00@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Віталій Тимчишин @ 2010-04-16 12:59 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
@ 2010-04-16 08:31 ` Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 12:49 ` Re: Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Yeb Havinga @ 2010-04-16 08:31 UTC (permalink / raw)
To: Віталій Тимчишин <tivv00@gmail.com>; +Cc: pgsql-performance
Віталій Тимчишин 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;
>
> (plan2.txt)
> Total runtime: 27.547 ms
>
> I've thought and someone in this list've told me that this should be
> done automatically.
Yes, if you have in a query a=b and b=c, then the optimizer figures out
that a=c as well. (a,b and c are then member of the same equivalence class).
However both queries are not the same, since the joins you're using are
outer joins. In the first it's possible that records are returned for
company records with no matching ces3_ records, the ces3_ records is
null in that case. In the second query no NULL ces3_ information may be
returned.
Another thing is it seems that the number of rows guessed is far off
from the actual number of rows, is the number 5000000 artificial or are
you're statistics old or too small histogram/mcv's?
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 08:31 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
@ 2010-04-16 12:49 ` Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 13:21 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Віталій Тимчишин @ 2010-04-16 12:49 UTC (permalink / raw)
To: Yeb Havinga <yebhavinga@gmail.com>; +Cc: pgsql-performance
16 квітня 2010 р. 11:31 Yeb Havinga <yebhavinga@gmail.com> написав:
> Віталій Тимчишин 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;
>>
>> (plan2.txt)
>> Total runtime: 27.547 ms
>>
>> I've thought and someone in this list've told me that this should be done
>> automatically.
>>
> Yes, if you have in a query a=b and b=c, then the optimizer figures out
> that a=c as well. (a,b and c are then member of the same equivalence class).
>
> However both queries are not the same, since the joins you're using are
> outer joins. In the first it's possible that records are returned for
> company records with no matching ces3_ records, the ces3_ records is null in
> that case. In the second query no NULL ces3_ information may be returned.
>
OK, but when I move limit to join condition the query is still fast:
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 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
and this_1_.company_id>50000000
order by this_.id asc limit 1000;
(plan3.txt),
Total runtime: 26.327 ms
BTW: Changing slow query to inner joins do not make it fast
>
> Another thing is it seems that the number of rows guessed is far off from
> the actual number of rows, is the number 5000000 artificial or are you're
> statistics old or too small histogram/mcv's?
>
Nope, I suppose this is because of limit. If I remove the limit, the
estimations are quite correct. There are ~6 millions of row in each table.
Attachments:
plan3.txt (text/plain, 1.7K) download
Limit (cost=293.40..1028.60 rows=1000 width=1209) (actual time=0.128..24.751 rows=1000 loops=1)
-> Merge Left Join (cost=293.40..4197731.11 rows=5709243 width=1209) (actual time=0.124..21.968 rows=1000 loops=1)
Merge Cond: (this_.id = (companymea2_.company_id)::bigint)
-> Merge Left Join (cost=246.59..3681230.10 rows=5709243 width=1141) (actual time=0.099..15.284 rows=1000 loops=1)
Merge Cond: (this_.id = (ces3_.company_id)::bigint)
-> Merge Join (cost=37.87..2435536.00 rows=5709243 width=639) (actual time=0.074..8.487 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=623) (actual time=0.037..1.860 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.758 rows=1000 loops=1)
Index Cond: ((this_1_.company_id)::bigint > 50000000)
Filter: ((this_1_.tag_id)::bigint = 7)
-> Index Scan using cws_company_descr_unique on company_descr ces3_ (cost=0.00..1169511.92 rows=5757068 width=502) (actual time=0.020..1.788 rows=1054 loops=1)
Index Cond: ((ces3_.company_id)::bigint > 50000000)
-> Index Scan using pk_comp_ms on company_measures companymea2_ (cost=0.00..440945.79 rows=5688885 width=68) (actual time=0.019..1.729 rows=1054 loops=1)
Index Cond: ((companymea2_.company_id)::bigint > 50000000)
Total runtime: 26.327 ms
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 08:31 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 12:49 ` Re: Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
@ 2010-04-16 13:21 ` Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 13:58 ` Re: Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Yeb Havinga @ 2010-04-16 13:21 UTC (permalink / raw)
To: Віталій Тимчишин <tivv00@gmail.com>; +Cc: pgsql-performance
Віталій Тимчишин wrote:
>
> BTW: Changing slow query to inner joins do not make it fast
I'm interested to see the query andplan of the slow query with inner joins.
>
>
> Another thing is it seems that the number of rows guessed is far
> off from the actual number of rows, is the number 5000000
> artificial or are you're statistics old or too small histogram/mcv's?
>
>
> Nope, I suppose this is because of limit. If I remove the limit, the
> estimations are quite correct. There are ~6 millions of row in each table.
Yes, that makes sense.
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 08:31 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 12:49 ` Re: Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 13:21 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
@ 2010-04-16 13:58 ` Віталій Тимчишин <tivv00@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Віталій Тимчишин @ 2010-04-16 13:58 UTC (permalink / raw)
To: Yeb Havinga <yebhavinga@gmail.com>; +Cc: pgsql-performance
16 квітня 2010 р. 16:21 Yeb Havinga <yebhavinga@gmail.com> написав:
> Віталій Тимчишин wrote:
>
>>
>> BTW: Changing slow query to inner joins do not make it fast
>>
> I'm interested to see the query andplan of the slow query with inner joins.
>
>
> Here you are. The query:
select * from company this_ inner join company_tag this_1_ on
this_.id=this_1_.company_id
inner join company_measures companymea2_ on
this_.id=companymea2_.company_id
inner join company_descr ces3_ on this_.id=ces3_.company_id
where this_1_.tag_id = 7 and this_.id>50000000
order by this_.id asc
limit 1000
;
Total runtime: 14088.942 ms
(plan is attached)
Best regards, Vitalii Tymchyshyn
Attachments:
innerplan.txt (text/plain, 1.5K) download
Limit (cost=227.15..883.22 rows=1000 width=1209) (actual time=14062.106..14087.375 rows=1000 loops=1)
-> Merge Join (cost=227.15..4355277.70 rows=6638046 width=1209) (actual time=14062.101..14084.577 rows=1000 loops=1)
Merge Cond: (this_.id = (this_1_.company_id)::bigint)
-> Merge Join (cost=185.14..4025978.59 rows=6757358 width=1193) (actual time=10692.975..10708.923 rows=1054 loops=1)
Merge Cond: ((companymea2_.company_id)::bigint = this_.id)
-> Merge Join (cost=0.00..1784574.44 rows=6821672 width=570) (actual time=0.111..9138.804 rows=1097096 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..456350.36 rows=6821672 width=68) (actual time=0.066..1747.291 rows=1097096 loops=1)
-> Index Scan using cws_company_descr_unique on company_descr ces3_ (cost=0.00..1225899.00 rows=6821672 width=502) (actual time=0.033..1822.085 rows=1097096 loops=1)
-> Index Scan using pk_comp_m on company this_ (cost=0.00..2139324.53 rows=6886598 width=623) (actual time=0.026..1.853 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..229167.56 rows=6765004 width=16) (actual time=0.028..1859.967 rows=1075634 loops=1)
Filter: ((this_1_.tag_id)::bigint = 7)
Total runtime: 14088.942 ms
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
@ 2010-04-16 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-16 15:33 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
2010-04-17 04:12 ` Re: Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2010-04-16 14:19 UTC (permalink / raw)
To: Віталій Тимчишин <tivv00@gmail.com>; +Cc: pgsql-performance
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com> writes:
> I've thought and someone in this list've told me that this should be done
> automatically.
No, that's not true. We do make deductions about transitive equalities,
ie, given WHERE a=b AND b=c the planner will infer a=c and use that if
it's helpful. We don't make deductions about inequalities such as a>c.
In theory there's enough information available to do so, but overall
trying to do that would probably waste more cycles than it would save.
You'd need a lot of expensive new planner infrastructure, and in the
vast majority of queries it wouldn't produce anything very helpful.
As was pointed out, even if we had such logic it wouldn't apply in this
example, because the equality conditions aren't real equalities but
OUTER JOIN conditions.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 14:19 ` Re: Planner not using column limit specified for one column for another column equal to first Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-04-16 15:33 ` Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 15:45 ` Re: Planner not using column limit specified for one column for another column equal to first Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Yeb Havinga @ 2010-04-16 15:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Віталій Тимчишин <tivv00@gmail.com>; pgsql-performance
Tom Lane wrote:
> =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com> writes:
>
>> I've thought and someone in this list've told me that this should be done
>> automatically.
>>
>
> No, that's not true. We do make deductions about transitive equalities,
> ie, given WHERE a=b AND b=c the planner will infer a=c and use that if
> it's helpful. We don't make deductions about inequalities such as a>c.
> In theory there's enough information available to do so, but overall
> trying to do that would probably waste more cycles than it would save.
> You'd need a lot of expensive new planner infrastructure, and in the
> vast majority of queries it wouldn't produce anything very helpful.
>
New expensive planner infrastructure to support from a>b and b>c infer
a>c, yes.
But I wonder if something like Leibniz's principle of identity holds for
members of the same equivalence class, e.g. like if x,y are both members
of the same EC, then for every predicate P, P(x) iff P(y). Probably not
for every predicate (like varno = 2 or attname='x'), but for the query
evaluation, the object denoted by the variables are the same, since that
is the standard meaning of the = operator. I cannot think of any
standard (btree) operator where 'Leibniz' would fail in this case.
regards,
Yeb Havinga
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 14:19 ` Re: Planner not using column limit specified for one column for another column equal to first Tom Lane <tgl@sss.pgh.pa.us>
2010-04-16 15:33 ` Re: Planner not using column limit specified for one column for another column equal to first Yeb Havinga <yebhavinga@gmail.com>
@ 2010-04-16 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2010-04-16 15:45 UTC (permalink / raw)
To: Yeb Havinga <yebhavinga@gmail.com>; +Cc: Віталій Тимчишин <tivv00@gmail.com>; pgsql-performance
Yeb Havinga <yebhavinga@gmail.com> writes:
> New expensive planner infrastructure to support from a>b and b>c infer
> a>c, yes.
> But I wonder if something like Leibniz's principle of identity holds for
> members of the same equivalence class, e.g. like if x,y are both members
> of the same EC, then for every predicate P, P(x) iff P(y).
This could only be assumed to apply for predicates constructed from
operators that are in the equivalence operator's btree opfamily.
Now, that would certainly be a large enough set of cases to sometimes
give useful results --- but I stand by the opinion that it wouldn't
win often enough to justify the added planner overhead.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 14:19 ` Re: Planner not using column limit specified for one column for another column equal to first Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-04-17 04:12 ` Віталій Тимчишин <tivv00@gmail.com>
2010-04-19 09:47 ` Re: Planner not using column limit specified for one column for another column equal to first Matthew Wakeling <matthew@flymine.org>
1 sibling, 1 reply; 12+ messages in thread
From: Віталій Тимчишин @ 2010-04-17 04:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
16 квітня 2010 р. 17:19 Tom Lane <tgl@sss.pgh.pa.us> написав:
> =?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com> writes:
> > I've thought and someone in this list've told me that this should be done
> > automatically.
>
> As was pointed out, even if we had such logic it wouldn't apply in this
> example, because the equality conditions aren't real equalities but
> OUTER JOIN conditions.
>
>
In this case you can copy condition to "ON" condition, not to where cause
and this would work correct, e.g. "select something from a join b on a.x=b.y
where a.x > n" <=> "select something from a join b on a.x=b.y and b.y > n
where a.x > n".
As of making planner more clever, may be it is possible to introduce
division on "fast queries" and "long queries", so that if after fast
planning cost is greater then some configurable threshold, advanced planning
techniques (or settings) are used. As far as I have seen in this list, many
techniques are not used simply because they are too complex and could make
planning take too much time for really fast queries, but they are vital for
long ones.
Also same (or similar) threshold could be used to enable replanning for each
run of prepared query - also an often complaint is that planned query is not
that fast as is could be.
--
Best regards,
Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Planner not using column limit specified for one column for another column equal to first
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 14:19 ` Re: Planner not using column limit specified for one column for another column equal to first Tom Lane <tgl@sss.pgh.pa.us>
2010-04-17 04:12 ` Re: Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
@ 2010-04-19 09:47 ` Matthew Wakeling <matthew@flymine.org>
0 siblings, 0 replies; 12+ messages in thread
From: Matthew Wakeling @ 2010-04-19 09:47 UTC (permalink / raw)
To: Віталій Тимчишин <tivv00@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Sat, 17 Apr 2010, Віталій Тимчишин wrote:
> As of making planner more clever, may be it is possible to introduce
> division on "fast queries" and "long queries", so that if after fast
> planning cost is greater then some configurable threshold, advanced planning
> techniques (or settings) are used. As far as I have seen in this list, many
> techniques are not used simply because they are too complex and could make
> planning take too much time for really fast queries, but they are vital for
> long ones.
+1. That's definitely a good idea in my view. The query optimiser I wrote
(which sits on top of Postgres and makes use of materialised views to
speed up queries) uses a similar approach - it expends effort proportional
to the estimated cost of the query, as reported by EXPLAIN.
Matthew
--
To most people, solutions mean finding the answers. But to chemists,
solutions are things that are still all mixed up.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2010-04-19 09:47 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-16 08:02 Planner not using column limit specified for one column for another column equal to first Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 08:25 ` Re: Planner not using column limit specified for one column for another column equal to first Hannu Krosing <hannu@2ndquadrant.com>
2010-04-16 12:59 ` Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 08:31 ` Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 12:49 ` Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 13:21 ` Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 13:58 ` Віталій Тимчишин <tivv00@gmail.com>
2010-04-16 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-16 15:33 ` Yeb Havinga <yebhavinga@gmail.com>
2010-04-16 15:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-17 04:12 ` Віталій Тимчишин <tivv00@gmail.com>
2010-04-19 09:47 ` Matthew Wakeling <matthew@flymine.org>
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