postgres=# \i crash.sql
version
──────────────────────────────────────────────────────────────────────────────────────────────────────────
PostgreSQL 9.1devel on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.4.4 20100630 (Red Hat 4.4.4…
…-10), 64-bit
(1 row)
Time: 0.979 ms
DROP TABLE
Time: 53.507 ms
CREATE TABLE
Time: 47.260 ms
INSERT 0 100000
Time: 6877.858 ms
ANALYZE
Time: 58.261 ms
CREATE INDEX
Time: 312.896 ms
CREATE INDEX
Time: 236.879 ms
psql:crash.sql:13: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:crash.sql:13: connection to server was lost
Regards
Pavel Stehule
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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
