Re: Crazy query plan.

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