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
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
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
