From: | Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: postmaster core dump |
Date: | 2005-09-19 20:18:30 |
Message-ID: | 20050919201830.GG11141@quartz.itdept.newn.cam.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, Sep 19, 2005 at 03:59:35PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> > I seem to have an unhappy postgresql:
>
> Let's see a test case, not a stack trace.
I haven't set up the minimalist test case yet, but the 2 tables involved
are incredibly simple. stats.id is an integer primary key, trans.stats_id
points to it. You just need a query which uses a HashJoin. This time, no
core dump, however:
transatlantic=# set enable_hashjoin=on;
SET
transatlantic=# select timeslice,count(stats_id) from trans,stats where trans.stats_id=stats.id group by timeslice;
timeslice | count
-----------+-------
(0 rows)
transatlantic=# set enable_hashjoin=off;
SET
transatlantic=# select timeslice,count(stats_id) from trans,stats where trans.stats_id=stats.id group by timeslice;
timeslice | count
---------------------+-------
2005-08-28 00:00:00 | 586
2005-08-28 00:00:01 | 378
2005-08-28 00:20:00 | 878
...
So, no results with enable_hashjoin=on.
Broken:
QUERY PLAN
--------------------------------------------------------------------------------------
GroupAggregate (cost=326296.78..338449.98 rows=97067 width=12)
-> Sort (cost=326296.78..329943.40 rows=1458648 width=12)
Sort Key: stats.timeslice
-> Hash Join (cost=4203.88..108728.93 rows=1458648 width=12)
Hash Cond: ("outer".stats_id = "inner".id)
-> Seq Scan on trans (cost=0.00..59706.48 rows=1458648 width=4)
-> Hash (cost=3292.30..3292.30 rows=123430 width=12)
-> Seq Scan on stats (cost=0.00..3292.30 rows=123430 width=12)
Working:
QUERY PLAN
---------------------------------------------------------------------------------------------------
GroupAggregate (cost=506460.77..518613.97 rows=97067 width=12)
-> Sort (cost=506460.77..510107.39 rows=1458648 width=12)
Sort Key: stats.timeslice
-> Merge Join (cost=263024.32..288892.93 rows=1458648 width=12)
Merge Cond: ("outer".id = "inner".stats_id)
-> Index Scan using stats_pkey on stats (cost=0.00..3688.21 rows=123430 width=12)
-> Sort (cost=263024.32..266670.94 rows=1458648 width=4)
Sort Key: trans.stats_id
-> Seq Scan on trans (cost=0.00..59706.48 rows=1458648 width=4)
I'll make a smaller test case over night..
Cheers,
Patrick
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2005-09-19 21:00:35 | Re: DISTINCT vs. GROUP BY |
Previous Message | Tom Lane | 2005-09-19 19:59:35 | Re: postmaster core dump |