Planning time grows exponentially with levels of nested views

From: "Joel Jacobson" <joel(at)compiler(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Planning time grows exponentially with levels of nested views
Date: 2021-04-18 18:58:53
Message-ID: 797aff54-b49b-4914-9ff9-aa42564a4d7d@www.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Hi,

I assumed the cost for each nested VIEW layer would grow linear,
but my testing shows it appears to grow exponentially:

CREATE TABLE foo (bar int);
INSERT INTO foo (bar) VALUES (123);

DO $_$
DECLARE
BEGIN
CREATE OR REPLACE VIEW v1 AS SELECT * FROM foo;
FOR i IN 1..256 LOOP
EXECUTE format
(
$$
CREATE OR REPLACE VIEW v%s AS
SELECT * FROM v%s
$$,
i+1,
i
);
END LOOP;
END
$_$;

EXPLAIN ANALYZE SELECT * FROM foo;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.004..0.004 rows=1 loops=1)
Planning Time: 0.117 ms
Execution Time: 0.011 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.003 rows=1 loops=1)
Planning Time: 0.019 ms
Execution Time: 0.015 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v2;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.002 rows=1 loops=1)
Planning Time: 0.018 ms
Execution Time: 0.011 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v4;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.002 rows=1 loops=1)
Planning Time: 0.030 ms
Execution Time: 0.013 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v8;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.002 rows=1 loops=1)
Planning Time: 0.061 ms
Execution Time: 0.016 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v16;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.003 rows=1 loops=1)
Planning Time: 0.347 ms
Execution Time: 0.027 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v32;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.002..0.003 rows=1 loops=1)
Planning Time: 2.096 ms
Execution Time: 0.044 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v64;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Planning Time: 14.981 ms
Execution Time: 0.119 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v128;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.004..0.004 rows=1 loops=1)
Planning Time: 109.407 ms
Execution Time: 0.187 ms
(3 rows)

EXPLAIN ANALYZE SELECT * FROM v256;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=2550 width=4) (actual time=0.006..0.007 rows=1 loops=1)
Planning Time: 1594.809 ms
Execution Time: 0.531 ms
(3 rows)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2021-04-18 20:10:50 Re: Vulnerability PostgreSQL 11.2
Previous Message Thomas Kellerer 2021-04-17 13:29:19 Re: Size of PostgreSQL backup ./. Sybase DUMP

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-04-18 19:08:44 Re: SQL-standard function body
Previous Message Noah Misch 2021-04-18 18:55:46 Re: SQL-standard function body