Search Postgresql Archives

Planning time grows exponentially with levels of nested views

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux