Planner questions
От | Joseph Shraibman |
---|---|
Тема | Planner questions |
Дата | |
Msg-id | 3F0DEBD2.1010404@selectacast.net обсуждение исходный текст |
Список | 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.
В списке pgsql-general по дате отправления: