From: Віталій Тимчишин <tivv00@gmail.com>
To: Yeb Havinga <yebhavinga@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 16:58:22 +0300
Message-ID: <u2s331e40661004160658s842c95d4x22d212dc8cdbbb4e@mail.gmail.com> (raw)
In-Reply-To: <4BC8645A.2060700@gmail.com>
References: <m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com>
<4BC8204A.7050904@gmail.com>
<k2u331e40661004160549td6bf86d5pb6fde70e6d1ab5d@mail.gmail.com>
<4BC8645A.2060700@gmail.com>
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
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, yebhavinga@gmail.com
Subject: Re: Planner not using column limit specified for one column for another column equal to first
In-Reply-To: <u2s331e40661004160658s842c95d4x22d212dc8cdbbb4e@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