strange explain in upstream - subplan 1 twice - is it bug?

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: strange explain in upstream - subplan 1 twice - is it bug?
Date: 2016-06-01 11:29:32
Message-ID: CAFj8pRAimq+NK-menjt+3J4-LFoodDD8Or6=Lc_stcFD+eD4DA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

When I tested some queries, I found strange plan

postgres=# explain analyze select s.nazev, o.nazev, o.pocet_obyvatel from
(select nazev, array(select id from obce_pocet_obyvatel where okresy.id =
okres_id order by pocet_obyvatel desc limit 3) as obceids from okresy) s
join obce_pocet_obyvatel o on o.id = ANY(obceids) order by 1, 3 desc;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐

QUERY
PLAN

╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Sort (cost=840.37..842.29 rows=769 width=24) (actual time=5.518..5.554
rows=227
loops=1)

│ Sort Key: okresy.nazev, ((obce.pocet_muzu + obce.pocet_zen))
DESC

│ Sort Method: quicksort Memory:
44kB

│ -> Nested Loop (cost=4.09..803.51 rows=769 width=24) (actual
time=0.132..4.735 rows=227
loops=1) │
│ -> Seq Scan on okresy (cost=0.00..1.77 rows=77 width=17)
(actual time=0.020..0.071 rows=77
loops=1) │
│ -> Index Scan using _obce_pkey on obce (cost=4.09..10.29
rows=10 width=22) (actual time=0.007..0.018 rows=3
loops=77) │
│ Index Cond: (id = ANY ((SubPlan
1)))

│ SubPlan
1

│ -> Limit (cost=0.28..3.81 rows=3 width=8) (actual
time=0.025..0.029 rows=3
loops=77) │
│ -> Index Scan using obce_okres_id_expr_idx on obce
obce_1 (cost=0.28..95.43 rows=81 width=8) (actual time=0.024..0.027 rows=3
loops=77) │
│ Index Cond: (okresy.id =
(okres_id)::text)

│ SubPlan
1

│ -> Limit (cost=0.28..3.81 rows=3 width=8) (actual
time=0.025..0.029 rows=3
loops=77) │
│ -> Index Scan using obce_okres_id_expr_idx on obce
obce_1 (cost=0.28..95.43 rows=81 width=8) (actual time=0.024..0.027 rows=3
loops=77) │
│ Index Cond: (okresy.id =
(okres_id)::text)

│ Planning time: 0.815
ms

│ Execution time: 5.693
ms

└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(17 rows)

test case is attached

tested on upstream.

Regards

Pavel

Attachment Content-Type Size
plan.txt text/plain 5.0 KB
obce.sql.tar.gz application/x-gzip 110.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2016-06-01 12:34:06 Re: Rename synchronous_standby_names?
Previous Message Teodor Sigaev 2016-06-01 11:25:49 Re: COMMENT ON, psql and access methods