Обсуждение: Crazy query plan.

SQL:
CREATE TABLE test (id BIGINT, id2 BIGINT, id3 BIGINT, id4 BIGINT);
INSERT INTO test SELECT i, i, i, i FROM generate_series(0, 9) i;
EXPLAIN ANALYZE SELECT ((SELECT tmp::test FROM (SELECT * FROM test
LIMIT 1) tmp)::test).*;

WILL:
QUERY PLAN
Result  (cost=0.11..0.12 rows=1 width=0) (actual time=0.076..0.078
rows=1 loops=1)
  InitPlan
    ->  Subquery Scan tmp  (cost=0.00..0.03 rows=1 width=32) (actual
time=0.014..0.019 rows=1 loops=1)
          ->  Limit  (cost=0.00..0.02 rows=1 width=32) (actual
time=0.008..0.009 rows=1 loops=1)
                ->  Seq Scan on test  (cost=0.00..23.10 rows=1310
width=32) (actual time=0.005..0.005 rows=1 loops=1)
    ->  Subquery Scan tmp  (cost=0.00..0.03 rows=1 width=32) (actual
time=0.008..0.013 rows=1 loops=1)
          ->  Limit  (cost=0.00..0.02 rows=1 width=32) (actual
time=0.005..0.006 rows=1 loops=1)
                ->  Seq Scan on test  (cost=0.00..23.10 rows=1310
width=32) (actual time=0.002..0.002 rows=1 loops=1)
    ->  Subquery Scan tmp  (cost=0.00..0.03 rows=1 width=32) (actual
time=0.008..0.012 rows=1 loops=1)
          ->  Limit  (cost=0.00..0.02 rows=1 width=32) (actual
time=0.005..0.006 rows=1 loops=1)
                ->  Seq Scan on test  (cost=0.00..23.10 rows=1310
width=32) (actual time=0.003..0.003 rows=1 loops=1)
    ->  Subquery Scan tmp  (cost=0.00..0.03 rows=1 width=32) (actual
time=0.009..0.013 rows=1 loops=1)
          ->  Limit  (cost=0.00..0.02 rows=1 width=32) (actual
time=0.005..0.006 rows=1 loops=1)
                ->  Seq Scan on test  (cost=0.00..23.10 rows=1310
width=32) (actual time=0.002..0.002 rows=1 loops=1)
Total runtime: 0.138 ms

One subquery for each column. wtf?

On 13/11/2009 7:25 PM, Oleg Serov wrote:
> EXPLAIN ANALYZE SELECT ((SELECT tmp::test FROM (SELECT * FROM test
> LIMIT 1) tmp)::test).*;

This may be simplified to the comparison between these two queries:


SELECT ((SELECT test FROM test LIMIT 1)::test);
SELECT ((SELECT test FROM test LIMIT 1)::test).*;



The former results in a single seq scan in a single subquery:

 Result  (cost=0.02..0.03 rows=1 width=0)
   InitPlan
     ->  Limit  (cost=0.00..0.02 rows=1 width=32)
           ->  Seq Scan on test  (cost=0.00..27.70 rows=1770 width=32)

The latter does this four times:

 Result  (cost=0.06..0.07 rows=1 width=0)
   InitPlan
     ->  Limit  (cost=0.00..0.02 rows=1 width=32)
           ->  Seq Scan on test  (cost=0.00..27.70 rows=1770 width=32)
     ->  Limit  (cost=0.00..0.02 rows=1 width=32)
           ->  Seq Scan on test  (cost=0.00..27.70 rows=1770 width=32)
     ->  Limit  (cost=0.00..0.02 rows=1 width=32)
           ->  Seq Scan on test  (cost=0.00..27.70 rows=1770 width=32)
     ->  Limit  (cost=0.00..0.02 rows=1 width=32)
           ->  Seq Scan on test  (cost=0.00..27.70 rows=1770 width=32)

The change is triggered by expansion of the single-ROW result of the
subquery into a regular 4-tuple.

Is the co0nversion of the ROW into individual fields in the SELECT
clause done by some kind of macro-expansion in parsing/planning?

--
Craig Ringer

> This may be simplified to the comparison between these two queries:

... or even further into:

CREATE TYPE test AS ( a integer, b integer, c integer, d integer );
EXPLAIN SELECT ((SELECT ROW(1,1,1,1)::test)::test);
EXPLAIN SELECT ((SELECT ROW(1,1,1,1)::test)::test).*;


craig=# EXPLAIN SELECT ((SELECT ROW(1,1,1,1)::test)::test);
                    QUERY PLAN
--------------------------------------------------
 Result  (cost=0.01..0.02 rows=1 width=0)
   InitPlan
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
(3 rows)

craig=# EXPLAIN SELECT ((SELECT ROW(1,1,1,1)::test)::test).*;
                    QUERY PLAN
--------------------------------------------------
 Result  (cost=0.04..0.05 rows=1 width=0)
   InitPlan
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
(6 rows)





Something odd I stumbled upon while testing for this:

craig=# SELECT tmp FROM (SELECT ((SELECT ROW(1,1,1,1)::test)::test)) AS tmp;
      tmp
---------------
 ("(1,1,1,1)")
(1 row)

... is fine,

craig=# SELECT tmp.* FROM (SELECT ((SELECT ROW(1,1,1,1)::test)::test))
AS tmp;
   test
-----------
 (1,1,1,1)
(1 row)

... is fine,

craig=# SELECT (tmp.*).* FROM (SELECT ((SELECT
ROW(1,1,1,1)::test)::test)) AS tmp;
   test
-----------
 (1,1,1,1)
(1 row)

... is ... WTF? How is "(tmp.*).*" the same as "tmp.*" ?


--
Craig Ringer

Sorry for the multiple replies-to-self, but this seemed worth
specifically noting: the expansion also results in multiple calls to
tuple-returning functions, even functions marked VOLATILE. For example:

CREATE OR REPLACE FUNCTION expandtest(INTEGER) RETURNS test AS $$
DECLARE
    rec test;
BEGIN
    RAISE NOTICE 'expandtest(%)',$1;
    rec.a := $1;
    rec.b := $1;
    rec.c := $1;
    rec.d := $1;
    RETURN rec;
END;
$$ LANGUAGE 'plpgsql' STRICT VOLATILE;


craig=# SELECT (expandtest(1)).*;
NOTICE:  expandtest(1)
NOTICE:  expandtest(1)
NOTICE:  expandtest(1)
NOTICE:  expandtest(1)
 a | b | c | d
---+---+---+---
 1 | 1 | 1 | 1
(1 row)


I don't know if that's intended behaviour or not, but I certainly find
it very _surprising_ behaviour, especially given the otherwise
equivalent translation:

craig=# SELECT t.* FROM expandtest(1) t;
NOTICE:  expandtest(1)
 a | b | c | d
---+---+---+---
 1 | 1 | 1 | 1
(1 row)