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 |
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 |