Re: postmaster core dump

On Mon, Sep 19, 2005 at 03:59:35PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.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 |   5862005-08-28 00:00:01 |   3782005-08-28 00:20:00 |   878
...

So, no results with enable_hashjoin=on.

Broken:
                                     QUERY PLAN                                      
--------------------------------------------------------------------------------------GroupAggregate
(cost=326296.78..338449.98rows=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=123430width=12)
 

Working:
                                           QUERY PLAN                                             
---------------------------------------------------------------------------------------------------GroupAggregate
(cost=506460.77..518613.97rows=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