Planner questions - Mailing list pgsql-general

For this query, explain shows that pg thinks it will be very fast(341) but it ends up
taking 119 seconds.  The query is SELECT s.field1, s.field2, ... (SELECT count(*) FROM
d),(SELECT count(*) FROM tablea),(SELECT count(*) FROM tablec).


                                                               QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=341.13..341.35 rows=88 width=204) (actual time=118786.24..118786.33 rows=89
loops=1)
    Sort Key: s.id
    ->  Nested Loop  (cost=0.00..338.29 rows=88 width=204) (actual
time=15167.25..118785.79 rows=89 loops=1)
          ->  Seq Scan on s  (cost=0.00..4.88 rows=88 width=157) (actual time=0.02..1.01
rows=89 loops=1)
          ->  Index Scan using d_pkey on d  (cost=0.00..3.78 rows=1 width=47) (actual
time=2.44..26.54 rows=1 loops=89)
                Index Cond: (<snip>)
          SubPlan
            ->  Aggregate  (cost=21.47..21.47 rows=1 width=0) (actual time=3.07..3.08
rows=1 loops=89)
                  ->  Seq Scan on tablea  (cost=0.00..21.46 rows=4 width=0) (actual
time=2.25..3.05 rows=2 loops=89)
                        Filter: (id = $0)
            ->  Aggregate  (cost=2.70..2.70 rows=1 width=0) (actual time=0.14..0.14 rows=1
loops=89)
                  ->  Seq Scan on tableb  (cost=0.00..2.70 rows=2 width=0) (actual
time=0.08..0.13 rows=2 loops=89)
                        Filter: (id = $0)
            ->  Aggregate  (cost=24724.43..24724.43 rows=1 width=0) (actual
time=1304.84..1304.85 rows=1 loops=89)
                  ->  Seq Scan on d  (cost=0.00..24649.89 rows=29818 width=0) (actual
time=208.54..1295.87 rows=7372 loops=89)
                        Filter: (id = $0)
  Total runtime: 118786.62 msec
(17 rows)


Then I thought I could speed up the query by putting the count at the top level instead of
in the subselect, but the sort required by the group by took up too much time:

                                                                          QUERY PLAN


----------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=284837.85..304517.58 rows=65599 width=212) (actual
time=154133.35..181258.06 rows=89 loops=1)
    ->  Group  (cost=284837.85..302877.60 rows=655991 width=212) (actual
time=154010.93..180196.14 rows=656111 loops=1)
          ->  Sort  (cost=284837.85..286477.82 rows=655991 width=212) (actual
time=154010.91..155404.21 rows=656111 loops=1)
                Sort Key: s.id, <nine other fields snipped>
                ->  Hash Join  (cost=338.51..36468.24 rows=655991 width=212) (actual
time=406.05..11540.73 rows=656111 loops=1)
                      Hash Cond: (<snip>)
                      ->  Seq Scan on d d2  (cost=0.00..23009.91 rows=655991 width=8)
(actual time=0.03..6505.98 rows=656115 loops=1)
                      ->  Hash  (cost=338.29..338.29 rows=88 width=204) (actual
time=405.94..405.94 rows=0 loops=1)
                            ->  Nested Loop  (cost=0.00..338.29 rows=88 width=204) (actual
time=0.14..405.68 rows=89 loops=1)
                                  ->  Seq Scan on s  (cost=0.00..4.88 rows=88 width=157)
(actual time=0.02..0.44 rows=89 loops=1)
                                  ->  Index Scan using d_pkey on d  (cost=0.00..3.78
rows=1 width=47) (actual time=2.80..4.54 rows=1 loops=89)
                                        Index Cond: (d.pkey = "outer".fieldc)
    SubPlan
      ->  Aggregate  (cost=21.47..21.47 rows=1 width=0) (actual time=1.08..1.08 rows=1
loops=89)
            ->  Seq Scan on application  (cost=0.00..21.46 rows=4 width=0) (actual
time=0.96..1.07 rows=2 loops=89)
                  Filter: (id = $0)
      ->  Aggregate  (cost=2.70..2.70 rows=1 width=0) (actual time=0.11..0.11 rows=1 loops=89)
            ->  Seq Scan on segmentmap  (cost=0.00..2.70 rows=2 width=0) (actual
time=0.05..0.10 rows=2 loops=89)
                  Filter: (id = $0)
  Total runtime: 181398.63 msec
(20 rows)

The hash takes 11.54 secs, but the sort takes 155.4 secs!  And the GROUP BY adds even more
time.