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)
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 |
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 |