Re: Doubts on startup costs

[Rajeev rastogi <rajeev.rastogi@xxxxxxxxxx>]

Start-up cost is the cost required to fetch the first tuple. So yes it is possible for the startup cost of innermost node/leaf node to be zero as show in below example:.

 

postgres=# explain select * from tbl,tbl2  where tbl2.id=tbl.id order by tbl.id;

                                    QUERY PLAN

----------------------------------------------------------------------------------

Merge Join  (cost=809.81..1636.50 rows=11571 width=8)

   Merge Cond: (tbl.id = tbl2.id)

   ->  Index Only Scan using idx on tbl  (cost=0.42..3369.01 rows=110000 width=4)

   ->  Sort  (cost=809.39..834.39 rows=10000 width=4)

         Sort Key: tbl2.id

         ->  Seq Scan on tbl2  (cost=0.00..145.00 rows=10000 width=4)

Planning time: 0.672 ms

 

postgres=# explain select * from tbl order by id;

                                 QUERY PLAN

----------------------------------------------------------------------------

Index Only Scan using idx on tbl  (cost=0.42..3369.01 rows=110000 width=4)

Planning time: 0.305 ms

 

Also start-up cost of outer node need not be more than total cost of inner nodes. If it is possible for outer nodes to

emit one tuple without waiting for complete operation to happen by inner nodes, then outer node start-up cost will be

much lesser than total cost by inner nodes. But start-up cost of outer node cannot be less the start-up cost of inner nodes.

 

For example in above example plan, a merge join can emit one tuple as soon as it finds one matching row.

So start-up cost is lesser.

 

Thanks and Regards,

Kumar Rajeev Rastogi

------------------------------------------------------------------------------------------------------------------------------
This e-mail and its attachments contain confidential information from HUAWEI, which
is intended only for the person or entity whose address is listed above. Any use of the
information contained herein in any way (including, but not limited to, total or partial
disclosure, reproduction, or dissemination) by persons other than the intended
recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by
phone or email immediately and delete it!

 

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Jayadevan M
Sent: 30 March 2014 20:45
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Doubts on startup costs

 

Hi,

A few academic questions related PostgreSQL query planner and output -

In the output of EXPLAIN for SQL statements I have seen so far, the startup cost for the innermost node/leaf has been 0. Are there situations where it may be non-zero?

The startup costs for outer nodes will always be equal to or greater than the total cost of the inner nodes? (My guess is NO, there may be cases where the outer node can start processing before the inner node is completely done).

Regards,

Jayadevan