crash.sql

text/x-sql

Download

SELECT version();

DROP TABLE IF EXISTS  foo;
CREATE TABLE foo(a int, b int);
INSERT INTO foo SELECT (random()*10000)::int, (random()*10)::int from generate_series(1,100000);
ANALYZE foo;
CREATE INDEX ON foo(a,b);
CREATE INDEX ON foo(b,a);

EXPLAIN SELECT max(a), b 
           FROM foo, generate_series(0,30) g(v) 
          WHERE a = (SELECT max(a) FROM foo WHERE b = v) 
          GROUP BY b;

/* sometimes necessary call more times
 * result without crash
postgres=# explain  select max(a), b from foo, generate_series(0,30)
g(v) where a = (select max(a) from foo where b = v) group by b;
                                                 QUERY PLAN                                                  
─────────────────────────────────────────────────────────────────────────────────────────────────────────────
 HashAggregate  (cost=445.57..445.58 rows=1 width=8)
   ->  Hash Join  (cost=22.50..445.54 rows=5 width=8)
         Hash Cond: (public.foo.a = (SubPlan 2))
         ->  Index Scan using foo_b_a_idx on foo  (cost=0.00..8.28 rows=1 width=8)
         ->  Hash  (cost=10.00..10.00 rows=1000 width=4)
               ->  Function Scan on generate_series g  (cost=0.00..10.00 rows=1000 width=4)
         SubPlan 2
           ->  Result  (cost=8.28..8.29 rows=1 width=0)
                 InitPlan 1 (returns $1)
                   ->  Limit  (cost=0.00..8.28 rows=1 width=4)
                         ->  Index Scan Backward using foo_a_b_idx1 on foo  (cost=0.00..8.28 rows=1 width=4)
                               Index Cond: ((a IS NOT NULL) AND (b = g.v))
(12 rows)

*
*/
EXPLAIN SELECT max(a), b 
           FROM foo, generate_series(0,30) g(v) 
          WHERE a = (SELECT max(a) FROM foo WHERE b = v) 
          GROUP BY b;