Planner not using column limit specified for one column for another column equal to first
| От | Віталій Тимчишин |
|---|---|
| Тема | Planner not using column limit specified for one column for another column equal to first |
| Дата | |
| Msg-id | m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Planner not using column limit specified for one column
for another column equal to first
Re: Planner not using column limit specified for one column for another column equal to first Re: Planner not using column limit specified for one column for another column equal to first |
| Список | 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
Вложения
В списке pgsql-performance по дате отправления: