Planning time grows exponentially with levels of nested views - Mailing list pgsql-general
| From | Joel Jacobson |
|---|---|
| Subject | Planning time grows exponentially with levels of nested views |
| Date | |
| Msg-id | 797aff54-b49b-4914-9ff9-aa42564a4d7d@www.fastmail.com Whole thread Raw |
| List | pgsql-general |
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)
pgsql-general by date: