pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedmerge join killing performance
16+ messages / 3 participants
[nested] [flat]
* merge join killing performance
@ 2010-05-19 00:17 Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2010-05-19 00:17 UTC (permalink / raw)
To: pgsql-performance
Machine: 8 core AMD opteron 2.1GHz, 12 disk RAID-10, 2 disk pg_xlog,
RHEL 5.4 pg version 8.3.9 (upgrading soon to 8.3.11 or so)
This query:
SELECT sum(f.bytes) AS sum FROM files f INNER JOIN events ev ON f.eid
= ev.eid WHERE ev.orgid = 969677;
is choosing a merge join, which never returns from explain analyze (it
might after 10 or so minutes, but I'm not beating up my production
server over it)
Aggregate (cost=902.41..902.42 rows=1 width=4)
-> Merge Join (cost=869.97..902.40 rows=1 width=4)
Merge Cond: (f.eid = ev.eid)
-> Index Scan using files_eid_idx on files f
(cost=0.00..157830.39 rows=3769434 width=8)
-> Sort (cost=869.52..872.02 rows=1002 width=4)
Sort Key: ev.eid
-> Index Scan using events_orgid_idx on events ev
(cost=0.00..819.57 rows=1002 width=4)
Index Cond: (orgid = 969677)
If I turn off mergejoin it's fast:
explain analyze SELECT sum(f.bytes) AS sum FROM files f INNER JOIN
events ev ON f.eid = ev.eid WHERE ev.orgid = 969677;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=3653.28..3653.29 rows=1 width=4) (actual
time=1.541..1.541 rows=1 loops=1)
-> Nested Loop (cost=0.00..3653.28 rows=1 width=4) (actual
time=1.537..1.537 rows=0 loops=1)
-> Index Scan using events_orgid_idx on events ev
(cost=0.00..819.57 rows=1002 width=4) (actual time=0.041..0.453
rows=185 loops=1)
Index Cond: (orgid = 969677)
-> Index Scan using files_eid_idx on files f
(cost=0.00..2.82 rows=1 width=8) (actual time=0.005..0.005 rows=0
loops=185)
Index Cond: (f.eid = ev.eid)
Total runtime: 1.637 ms
I've played around with random_page_cost. All the other things you'd
expect, like effective_cache_size are set rather large (it's a server
with 32Gig ram and a 12 disk RAID-10) and no setting of
random_page_cost forces it to choose the non-mergejoin plan.
Anybody with any ideas, I'm all ears.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-19 03:00 ` Matthew Wakeling <matthew@flymine.org>
2010-05-19 03:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 16+ messages in thread
From: Matthew Wakeling @ 2010-05-19 03:00 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
On Tue, 18 May 2010, Scott Marlowe wrote:
> Aggregate (cost=902.41..902.42 rows=1 width=4)
> -> Merge Join (cost=869.97..902.40 rows=1 width=4)
> Merge Cond: (f.eid = ev.eid)
> -> Index Scan using files_eid_idx on files f
> (cost=0.00..157830.39 rows=3769434 width=8)
Okay, that's weird. How is the cost of the merge join only 902, when the
cost of one of the branches 157830, when there is no LIMIT?
Are the statistics up to date?
Matthew
--
As you approach the airport, you see a sign saying "Beware - low
flying airplanes". There's not a lot you can do about that. Take
your hat off? -- Michael Flanders
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
@ 2010-05-19 03:06 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Scott Marlowe @ 2010-05-19 03:06 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Tue, May 18, 2010 at 9:00 PM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Tue, 18 May 2010, Scott Marlowe wrote:
>>
>> Aggregate (cost=902.41..902.42 rows=1 width=4)
>> -> Merge Join (cost=869.97..902.40 rows=1 width=4)
>> Merge Cond: (f.eid = ev.eid)
>> -> Index Scan using files_eid_idx on files f
>> (cost=0.00..157830.39 rows=3769434 width=8)
>
> Okay, that's weird. How is the cost of the merge join only 902, when the
> cost of one of the branches 157830, when there is no LIMIT?
>
> Are the statistics up to date?
Yep. The explain analyze shows it being close enough it should guess
right (I think) We have default stats target set to 200 and the table
is regularly analyzed by autovac, which now has much smaller settings
for threshold and % than default to handle these big tables.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
@ 2010-05-19 16:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 17:08 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 2 replies; 16+ messages in thread
From: Tom Lane @ 2010-05-19 16:53 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-performance
Matthew Wakeling <matthew@flymine.org> writes:
> On Tue, 18 May 2010, Scott Marlowe wrote:
>> Aggregate (cost=902.41..902.42 rows=1 width=4)
>> -> Merge Join (cost=869.97..902.40 rows=1 width=4)
>> Merge Cond: (f.eid = ev.eid)
>> -> Index Scan using files_eid_idx on files f
>> (cost=0.00..157830.39 rows=3769434 width=8)
> Okay, that's weird. How is the cost of the merge join only 902, when the
> cost of one of the branches 157830, when there is no LIMIT?
It's apparently estimating (wrongly) that the merge join won't have to
scan very much of "files" before it can stop because it finds an eid
value larger than any eid in the other table. So the issue here is an
inexact stats value for the max eid.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-05-19 17:08 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Scott Marlowe @ 2010-05-19 17:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Wed, May 19, 2010 at 10:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Matthew Wakeling <matthew@flymine.org> writes:
>> On Tue, 18 May 2010, Scott Marlowe wrote:
>>> Aggregate (cost=902.41..902.42 rows=1 width=4)
>>> -> Merge Join (cost=869.97..902.40 rows=1 width=4)
>>> Merge Cond: (f.eid = ev.eid)
>>> -> Index Scan using files_eid_idx on files f
>>> (cost=0.00..157830.39 rows=3769434 width=8)
>
>> Okay, that's weird. How is the cost of the merge join only 902, when the
>> cost of one of the branches 157830, when there is no LIMIT?
>
> It's apparently estimating (wrongly) that the merge join won't have to
> scan very much of "files" before it can stop because it finds an eid
> value larger than any eid in the other table. So the issue here is an
> inexact stats value for the max eid.
That's a big table. I'll try cranking up the stats target for that
column and see what happens. Thanks!
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-05-19 20:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 20:47 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
1 sibling, 2 replies; 16+ messages in thread
From: Scott Marlowe @ 2010-05-19 20:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Wed, May 19, 2010 at 10:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Matthew Wakeling <matthew@flymine.org> writes:
>> On Tue, 18 May 2010, Scott Marlowe wrote:
>>> Aggregate (cost=902.41..902.42 rows=1 width=4)
>>> -> Merge Join (cost=869.97..902.40 rows=1 width=4)
>>> Merge Cond: (f.eid = ev.eid)
>>> -> Index Scan using files_eid_idx on files f
>>> (cost=0.00..157830.39 rows=3769434 width=8)
>
>> Okay, that's weird. How is the cost of the merge join only 902, when the
>> cost of one of the branches 157830, when there is no LIMIT?
>
> It's apparently estimating (wrongly) that the merge join won't have to
> scan very much of "files" before it can stop because it finds an eid
> value larger than any eid in the other table. So the issue here is an
> inexact stats value for the max eid.
I changed stats target to 1000 for that field and still get the bad plan.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-19 20:47 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Scott Marlowe @ 2010-05-19 20:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Wed, May 19, 2010 at 2:27 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, May 19, 2010 at 10:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Matthew Wakeling <matthew@flymine.org> writes:
>>> On Tue, 18 May 2010, Scott Marlowe wrote:
>>>> Aggregate (cost=902.41..902.42 rows=1 width=4)
>>>> -> Merge Join (cost=869.97..902.40 rows=1 width=4)
>>>> Merge Cond: (f.eid = ev.eid)
>>>> -> Index Scan using files_eid_idx on files f
>>>> (cost=0.00..157830.39 rows=3769434 width=8)
>>
>>> Okay, that's weird. How is the cost of the merge join only 902, when the
>>> cost of one of the branches 157830, when there is no LIMIT?
>>
>> It's apparently estimating (wrongly) that the merge join won't have to
>> scan very much of "files" before it can stop because it finds an eid
>> value larger than any eid in the other table. So the issue here is an
>> inexact stats value for the max eid.
>
> I changed stats target to 1000 for that field and still get the bad plan.
And of course ran analyze across the table...
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-20 01:46 ` Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Matthew Wakeling @ 2010-05-20 01:46 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Wed, 19 May 2010, Scott Marlowe wrote:
>> It's apparently estimating (wrongly) that the merge join won't have to
>> scan very much of "files" before it can stop because it finds an eid
>> value larger than any eid in the other table. So the issue here is an
>> inexact stats value for the max eid.
I wandered if it could be something like that, but I rejected that idea,
as it obviously wasn't the real world case, and statistics should at least
get that right, if they are up to date.
> I changed stats target to 1000 for that field and still get the bad plan.
What do the stats say the max values are?
Matthew
--
Nog: Look! They've made me into an ensign!
O'Brien: I didn't know things were going so badly.
Nog: Frightening, isn't it?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
@ 2010-05-20 02:04 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2010-05-20 02:04 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Wed, May 19, 2010 at 7:46 PM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Wed, 19 May 2010, Scott Marlowe wrote:
>>>
>>> It's apparently estimating (wrongly) that the merge join won't have to
>>> scan very much of "files" before it can stop because it finds an eid
>>> value larger than any eid in the other table. So the issue here is an
>>> inexact stats value for the max eid.
>
> I wandered if it could be something like that, but I rejected that idea, as
> it obviously wasn't the real world case, and statistics should at least get
> that right, if they are up to date.
>
>> I changed stats target to 1000 for that field and still get the bad plan.
>
> What do the stats say the max values are?
5277063,5423043,13843899 (I think).
# select count(distinct eid) from files;
count
-------
365
(1 row)
# select count(*) from files;
count
---------
3793748
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-20 02:06 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:07 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2010-05-20 02:06 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Wed, May 19, 2010 at 8:04 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, May 19, 2010 at 7:46 PM, Matthew Wakeling <matthew@flymine.org> wrote:
>> On Wed, 19 May 2010, Scott Marlowe wrote:
>>>>
>>>> It's apparently estimating (wrongly) that the merge join won't have to
>>>> scan very much of "files" before it can stop because it finds an eid
>>>> value larger than any eid in the other table. So the issue here is an
>>>> inexact stats value for the max eid.
>>
>> I wandered if it could be something like that, but I rejected that idea, as
>> it obviously wasn't the real world case, and statistics should at least get
>> that right, if they are up to date.
>>
>>> I changed stats target to 1000 for that field and still get the bad plan.
>>
>> What do the stats say the max values are?
>
> 5277063,5423043,13843899 (I think).
>
> # select count(distinct eid) from files;
> count
> -------
> 365
> (1 row)
>
> # select count(*) from files;
> count
> ---------
> 3793748
A followup. of those rows,
select count(*) from files where eid is null;
count
---------
3793215
are null.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-20 02:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 14:28 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-28 01:16 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 16+ messages in thread
From: Scott Marlowe @ 2010-05-20 02:07 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Wed, May 19, 2010 at 8:06 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, May 19, 2010 at 8:04 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Wed, May 19, 2010 at 7:46 PM, Matthew Wakeling <matthew@flymine.org> wrote:
>>> On Wed, 19 May 2010, Scott Marlowe wrote:
>>>>>
>>>>> It's apparently estimating (wrongly) that the merge join won't have to
>>>>> scan very much of "files" before it can stop because it finds an eid
>>>>> value larger than any eid in the other table. So the issue here is an
>>>>> inexact stats value for the max eid.
>>>
>>> I wandered if it could be something like that, but I rejected that idea, as
>>> it obviously wasn't the real world case, and statistics should at least get
>>> that right, if they are up to date.
>>>
>>>> I changed stats target to 1000 for that field and still get the bad plan.
>>>
>>> What do the stats say the max values are?
>>
>> 5277063,5423043,13843899 (I think).
>>
>> # select count(distinct eid) from files;
>> count
>> -------
>> 365
>> (1 row)
>>
>> # select count(*) from files;
>> count
>> ---------
>> 3793748
>
> A followup. of those rows,
>
> select count(*) from files where eid is null;
> count
> ---------
> 3793215
>
> are null.
So, Tom, so you think it's possible that the planner isn't noticing
all those nulls and thinks it'll just take a row or two to get to the
value it needs to join on?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:07 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-20 14:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-20 14:35 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Tom Lane @ 2010-05-20 14:28 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Scott Marlowe <scott.marlowe@gmail.com> writes:
> So, Tom, so you think it's possible that the planner isn't noticing
> all those nulls and thinks it'll just take a row or two to get to the
> value it needs to join on?
Could be. I don't have time right now to chase through the code, but
that sounds like a plausible theory.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:07 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 14:28 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-05-20 14:35 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Scott Marlowe @ 2010-05-20 14:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Thu, May 20, 2010 at 8:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Scott Marlowe <scott.marlowe@gmail.com> writes:
>> So, Tom, so you think it's possible that the planner isn't noticing
>> all those nulls and thinks it'll just take a row or two to get to the
>> value it needs to join on?
>
> Could be. I don't have time right now to chase through the code, but
> that sounds like a plausible theory.
K. I think I'll try an index on that field "where not null" and see
if that helps.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:07 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-28 01:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-28 02:46 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Tom Lane @ 2010-05-28 01:16 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Scott Marlowe <scott.marlowe@gmail.com> writes:
> So, Tom, so you think it's possible that the planner isn't noticing
> all those nulls and thinks it'll just take a row or two to get to the
> value it needs to join on?
I've committed a patch for this, if you're interested in testing that
it fixes your situation.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:07 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-28 01:16 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-05-28 02:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-28 02:56 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 16+ messages in thread
From: Scott Marlowe @ 2010-05-28 02:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Thu, May 27, 2010 at 7:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Scott Marlowe <scott.marlowe@gmail.com> writes:
>> So, Tom, so you think it's possible that the planner isn't noticing
>> all those nulls and thinks it'll just take a row or two to get to the
>> value it needs to join on?
>
> I've committed a patch for this, if you're interested in testing that
> it fixes your situation.
Cool, do we have a snapshot build somewhere or do I need to get all
the extra build bits like flex or yacc or bison or whatnot?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: merge join killing performance
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-19 16:53 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 20:27 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Re: merge join killing performance Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:07 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-28 01:16 ` Re: merge join killing performance Tom Lane <tgl@sss.pgh.pa.us>
2010-05-28 02:46 ` Re: merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-05-28 02:56 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2010-05-28 02:56 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Thu, May 27, 2010 at 7:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I've committed a patch for this, if you're interested in testing that
>> it fixes your situation.
> Cool, do we have a snapshot build somewhere or do I need to get all
> the extra build bits like flex or yacc or bison or whatnot?
There's a nightly snapshot tarball of HEAD on the ftp server.
I don't believe there's any snapshots for back branches though.
Alternatively, you could grab the latest release tarball for whichever
branch you want and just apply that patch --- it should apply cleanly.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2010-05-28 02:56 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-19 00:17 merge join killing performance Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 03:00 ` Matthew Wakeling <matthew@flymine.org>
2010-05-19 03:06 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 16:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-19 17:08 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 20:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-19 20:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 01:46 ` Matthew Wakeling <matthew@flymine.org>
2010-05-20 02:04 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:06 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 02:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-20 14:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-20 14:35 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-28 01:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-05-28 02:46 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-05-28 02:56 ` Tom Lane <tgl@sss.pgh.pa.us>
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