X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 84E675E3639 for ; Sun, 15 Aug 2004 13:00:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18945-07 for ; Sun, 15 Aug 2004 16:00:20 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 384CB5E46DB for ; Sun, 15 Aug 2004 13:00:11 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.12.11/8.12.11) with ESMTP id i7FG0EOw041064 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Sun, 15 Aug 2004 10:00:17 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.12.11/8.12.11) with ESMTP id i7FG0EEl085302 for ; Sun, 15 Aug 2004 10:00:14 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.12.11/8.12.11/Submit) id i7FG0Ert085301 for pgsql-performance@postgresql.org; Sun, 15 Aug 2004 10:00:14 -0600 (MDT) (envelope-from mfuhr) Date: Sun, 15 Aug 2004 10:00:14 -0600 From: Michael Fuhr To: pgsql-performance@postgresql.org Subject: Slow joins against set-returning functions Message-ID: <20040815160014.GA85211@winnie.fuhr.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/236 X-Sequence-Number: 7872 PostgreSQL versions: 7.4.3, 8.0.0beta1 Joins against set-returning functions can be slow. Here's a simple example (in 8.0.0beta1, the gen_series function can be replaced with generate_series): CREATE FUNCTION gen_series(INTEGER, INTEGER) RETURNS SETOF INTEGER AS ' DECLARE xstart ALIAS FOR $1; xend ALIAS FOR $2; x INTEGER; BEGIN FOR x IN xstart .. xend LOOP RETURN NEXT x; END LOOP; RETURN; END; ' LANGUAGE plpgsql IMMUTABLE STRICT; CREATE TABLE stuff ( id INTEGER NOT NULL PRIMARY KEY, item VARCHAR(32) NOT NULL ); INSERT INTO stuff (id, item) SELECT id, 'Item ' || id FROM gen_series(1, 100000) AS f(id); ANALYZE stuff; Here are two queries; notice how the second query, which uses higher numbers for the join key, is much slower than the first, apparently due to an inefficient index scan: EXPLAIN ANALYZE SELECT stuff.* FROM stuff JOIN gen_series(1, 10) AS f(id) USING (id); Merge Join (cost=62.33..2544.33 rows=1001 width=17) (actual time=1.398..1.950 rows=10 loops=1) Merge Cond: ("outer".id = "inner".id) -> Index Scan using stuff_pkey on stuff (cost=0.00..2217.00 rows=100000 width=17) (actual time=0.667..0.860 rows=11 loops=1) -> Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=0.646..0.670 rows=10 loops=1) Sort Key: f.id -> Function Scan on gen_series f (cost=0.00..12.50 rows=1000 width=4) (actual time=0.403..0.478 rows=10 loops=1) Total runtime: 2.529 ms (7 rows) EXPLAIN ANALYZE SELECT stuff.* FROM stuff JOIN gen_series(99991, 100000) AS f(id) USING (id); Merge Join (cost=62.33..2544.33 rows=1001 width=17) (actual time=2907.078..2907.618 rows=10 loops=1) Merge Cond: ("outer".id = "inner".id) -> Index Scan using stuff_pkey on stuff (cost=0.00..2217.00 rows=100000 width=17) (actual time=0.107..2270.722 rows=100000 loops=1) -> Sort (cost=62.33..64.83 rows=1000 width=4) (actual time=0.630..0.654 rows=10 loops=1) Sort Key: f.id -> Function Scan on gen_series f (cost=0.00..12.50 rows=1000 width=4) (actual time=0.392..0.469 rows=10 loops=1) Total runtime: 2908.205 ms (7 rows) If I turn off enable_mergejoin then both queries are fast: SET enable_mergejoin TO off; EXPLAIN ANALYZE SELECT stuff.* FROM stuff JOIN gen_series(1, 10) AS f(id) USING (id); Nested Loop (cost=0.00..3038.50 rows=1001 width=17) (actual time=0.600..1.912 rows=10 loops=1) -> Function Scan on gen_series f (cost=0.00..12.50 rows=1000 width=4) (actual time=0.395..0.482 rows=10 loops=1) -> Index Scan using stuff_pkey on stuff (cost=0.00..3.01 rows=1 width=17) (actual time=0.091..0.107 rows=1 loops=10) Index Cond: (stuff.id = "outer".id) Total runtime: 2.401 ms (5 rows) EXPLAIN ANALYZE SELECT stuff.* FROM stuff JOIN gen_series(99991, 100000) AS f(id) USING (id); Nested Loop (cost=0.00..3038.50 rows=1001 width=17) (actual time=0.586..1.891 rows=10 loops=1) -> Function Scan on gen_series f (cost=0.00..12.50 rows=1000 width=4) (actual time=0.394..0.479 rows=10 loops=1) -> Index Scan using stuff_pkey on stuff (cost=0.00..3.01 rows=1 width=17) (actual time=0.089..0.105 rows=1 loops=10) Index Cond: (stuff.id = "outer".id) Total runtime: 2.374 ms (5 rows) Is the planner doing something wrong here? Thanks. -- Michael Fuhr http://www.fuhr.org/~mfuhr/