By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
version |
---|
PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit |
21 rows affected
lpo_id | lang_1 | lang_2 | least | greatest |
---|---|---|---|---|
1 | 6 | 4 | 4 | 6 |
2 | 4 | 6 | 4 | 6 |
3 | 6 | 68 | 6 | 68 |
4 | 68 | 6 | 6 | 68 |
5 | 18 | 6 | 6 | 18 |
6 | 6 | 18 | 6 | 18 |
7 | 36 | 18 | 18 | 36 |
8 | 18 | 36 | 18 | 36 |
9 | 1 | 2 | 1 | 2 |
10 | 1 | 3 | 1 | 3 |
11 | 1 | 4 | 1 | 4 |
12 | 1 | 5 | 1 | 5 |
13 | 1 | 6 | 1 | 6 |
14 | 1 | 7 | 1 | 7 |
15 | 1 | 8 | 1 | 8 |
16 | 2 | 3 | 2 | 3 |
17 | 2 | 4 | 2 | 4 |
18 | 2 | 5 | 2 | 5 |
19 | 2 | 6 | 2 | 6 |
20 | 2 | 7 | 2 | 7 |
21 | 2 | 8 | 2 | 8 |
lpo_id | least | greatest | rn |
---|---|---|---|
1 | 4 | 6 | 2 |
2 | 4 | 6 | 1 |
3 | 6 | 68 | 2 |
4 | 6 | 68 | 1 |
5 | 6 | 18 | 2 |
6 | 6 | 18 | 1 |
7 | 18 | 36 | 2 |
8 | 18 | 36 | 1 |
9 | 1 | 2 | 1 |
10 | 1 | 3 | 1 |
11 | 1 | 4 | 1 |
12 | 1 | 5 | 1 |
13 | 1 | 6 | 1 |
14 | 1 | 7 | 1 |
15 | 1 | 8 | 1 |
16 | 2 | 3 | 1 |
17 | 2 | 4 | 1 |
18 | 2 | 5 | 1 |
19 | 2 | 6 | 1 |
20 | 2 | 7 | 1 |
21 | 2 | 8 | 1 |
lpo_id | least | greatest | rn |
---|---|---|---|
1 | 4 | 6 | 2 |
2 | 4 | 6 | 1 |
3 | 6 | 68 | 2 |
4 | 6 | 68 | 1 |
5 | 6 | 18 | 2 |
6 | 6 | 18 | 1 |
7 | 18 | 36 | 2 |
8 | 18 | 36 | 1 |
9 | 1 | 2 | 1 |
10 | 1 | 3 | 1 |
11 | 1 | 4 | 1 |
12 | 1 | 5 | 1 |
13 | 1 | 6 | 1 |
14 | 1 | 7 | 1 |
15 | 1 | 8 | 1 |
16 | 2 | 3 | 1 |
17 | 2 | 4 | 1 |
18 | 2 | 5 | 1 |
19 | 2 | 6 | 1 |
20 | 2 | 7 | 1 |
21 | 2 | 8 | 1 |
ERROR: syntax error at or near "t"
LINE 10: DELETE t
^
ERROR: syntax error at or near "t"
LINE 2: DELETE t
^
lpo_id | least | greatest |
---|---|---|
1 | 4 | 6 |
2 | 4 | 6 |
3 | 6 | 68 |
4 | 6 | 68 |
5 | 6 | 18 |
6 | 6 | 18 |
7 | 18 | 36 |
8 | 18 | 36 |
9 | 1 | 2 |
10 | 1 | 3 |
11 | 1 | 4 |
12 | 1 | 5 |
13 | 1 | 6 |
14 | 1 | 7 |
15 | 1 | 8 |
16 | 2 | 3 |
17 | 2 | 4 |
18 | 2 | 5 |
19 | 2 | 6 |
20 | 2 | 7 |
21 | 2 | 8 |
QUERY PLAN |
---|
Delete on public.test t (cost=208.64..286.93 rows=680 width=34) (actual time=0.147..0.149 rows=0 loops=1) |
Buffers: shared hit=6 |
-> Hash Join (cost=208.64..286.93 rows=680 width=34) (actual time=0.121..0.132 rows=4 loops=1) |
Output: t.ctid, tab.* |
Inner Unique: true |
Hash Cond: (tab.lpo_id = t.lpo_id) |
Buffers: shared hit=2 |
-> Subquery Scan on tab (cost=152.74..229.24 rows=680 width=32) (actual time=0.071..0.080 rows=4 loops=1) |
Output: tab.*, tab.lpo_id |
Filter: (tab.rn > 1) |
Rows Removed by Filter: 17 |
Buffers: shared hit=1 |
-> WindowAgg (cost=152.74..203.74 rows=2040 width=20) (actual time=0.030..0.046 rows=21 loops=1) |
Output: t_1.lpo_id, (LEAST(t_1.lang_1, t_1.lang_2)), (GREATEST(t_1.lang_1, t_1.lang_2)), row_number() OVER (?) |
Buffers: shared hit=1 |
-> Sort (cost=152.74..157.84 rows=2040 width=12) (actual time=0.024..0.026 rows=21 loops=1) |
Output: (LEAST(t_1.lang_1, t_1.lang_2)), (GREATEST(t_1.lang_1, t_1.lang_2)), t_1.lpo_id |
Sort Key: (LEAST(t_1.lang_1, t_1.lang_2)), (GREATEST(t_1.lang_1, t_1.lang_2)) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test t_1 (cost=0.00..40.60 rows=2040 width=12) (actual time=0.005..0.008 rows=21 loops=1) |
Output: LEAST(t_1.lang_1, t_1.lang_2), GREATEST(t_1.lang_1, t_1.lang_2), t_1.lpo_id |
Buffers: shared hit=1 |
-> Hash (cost=30.40..30.40 rows=2040 width=10) (actual time=0.025..0.025 rows=21 loops=1) |
Output: t.ctid, t.lpo_id |
Buckets: 2048 Batches: 1 Memory Usage: 17kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test t (cost=0.00..30.40 rows=2040 width=10) (actual time=0.007..0.010 rows=21 loops=1) |
Output: t.ctid, t.lpo_id |
Buffers: shared hit=1 |
Planning Time: 0.180 ms |
Execution Time: 0.217 ms |
QUERY PLAN |
---|
Delete on public.test (cost=352.62..395.94 rows=680 width=34) (actual time=0.081..0.082 rows=0 loops=1) |
Buffers: shared hit=2 |
-> Hash Join (cost=352.62..395.94 rows=680 width=34) (actual time=0.080..0.081 rows=0 loops=1) |
Output: test.ctid, tab.* |
Inner Unique: true |
Hash Cond: (test.lpo_id = tab.lpo_id) |
Buffers: shared hit=2 |
-> Seq Scan on public.test (cost=0.00..30.40 rows=2040 width=10) (actual time=0.010..0.010 rows=1 loops=1) |
Output: test.ctid, test.lpo_id |
Buffers: shared hit=1 |
-> Hash (cost=350.15..350.15 rows=197 width=32) (actual time=0.058..0.059 rows=0 loops=1) |
Output: tab.*, tab.lpo_id |
Buckets: 1024 Batches: 1 Memory Usage: 8kB |
Buffers: shared hit=1 |
-> HashAggregate (cost=348.18..350.15 rows=197 width=32) (actual time=0.058..0.058 rows=0 loops=1) |
Output: tab.*, tab.lpo_id |
Group Key: tab.lpo_id |
Buffers: shared hit=1 |
-> Subquery Scan on tab (cost=315.88..346.48 rows=680 width=32) (actual time=0.055..0.056 rows=0 loops=1) |
Output: tab.*, tab.lpo_id |
Filter: (tab.rn > 1) |
Rows Removed by Filter: 17 |
Buffers: shared hit=1 |
-> Sort (cost=315.88..320.98 rows=2040 width=20) (actual time=0.051..0.053 rows=17 loops=1) |
Output: t.lpo_id, (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2)), (row_number() OVER (?)) |
Sort Key: t.lpo_id |
Sort Method: quicksort Memory: 26kB |
Buffers: shared hit=1 |
-> WindowAgg (cost=152.74..203.74 rows=2040 width=20) (actual time=0.027..0.041 rows=17 loops=1) |
Output: t.lpo_id, (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2)), row_number() OVER (?) |
Buffers: shared hit=1 |
-> Sort (cost=152.74..157.84 rows=2040 width=12) (actual time=0.020..0.021 rows=17 loops=1) |
Output: (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2)), t.lpo_id |
Sort Key: (LEAST(t.lang_1, t.lang_2)), (GREATEST(t.lang_1, t.lang_2)) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test t (cost=0.00..40.60 rows=2040 width=12) (actual time=0.006..0.009 rows=17 loops=1) |
Output: LEAST(t.lang_1, t.lang_2), GREATEST(t.lang_1, t.lang_2), t.lpo_id |
Buffers: shared hit=1 |
Planning Time: 0.204 ms |
Execution Time: 0.149 ms |
QUERY PLAN |
---|
Delete on public.test t (cost=235.37..278.69 rows=680 width=34) (actual time=0.059..0.061 rows=0 loops=1) |
Buffers: shared hit=2 |
-> Hash Join (cost=235.37..278.69 rows=680 width=34) (actual time=0.059..0.060 rows=0 loops=1) |
Output: t.ctid, cte.* |
Inner Unique: true |
Hash Cond: (t.lpo_id = cte.lpo_id) |
Buffers: shared hit=2 |
-> Seq Scan on public.test t (cost=0.00..30.40 rows=2040 width=10) (actual time=0.010..0.010 rows=1 loops=1) |
Output: t.ctid, t.lpo_id |
Buffers: shared hit=1 |
-> Hash (cost=232.91..232.91 rows=197 width=32) (actual time=0.045..0.046 rows=0 loops=1) |
Output: cte.*, cte.lpo_id |
Buckets: 1024 Batches: 1 Memory Usage: 8kB |
Buffers: shared hit=1 |
-> HashAggregate (cost=230.94..232.91 rows=197 width=32) (actual time=0.045..0.045 rows=0 loops=1) |
Output: cte.*, cte.lpo_id |
Group Key: cte.lpo_id |
Buffers: shared hit=1 |
-> Subquery Scan on cte (cost=152.74..229.24 rows=680 width=32) (actual time=0.043..0.044 rows=0 loops=1) |
Output: cte.*, cte.lpo_id |
Filter: (cte.rn > 1) |
Rows Removed by Filter: 17 |
Buffers: shared hit=1 |
-> WindowAgg (cost=152.74..203.74 rows=2040 width=20) (actual time=0.027..0.041 rows=17 loops=1) |
Output: test.lpo_id, (LEAST(test.lang_1, test.lang_2)), (GREATEST(test.lang_1, test.lang_2)), row_number() OVER (?) |
Buffers: shared hit=1 |
-> Sort (cost=152.74..157.84 rows=2040 width=12) (actual time=0.016..0.018 rows=17 loops=1) |
Output: (LEAST(test.lang_1, test.lang_2)), (GREATEST(test.lang_1, test.lang_2)), test.lpo_id |
Sort Key: (LEAST(test.lang_1, test.lang_2)), (GREATEST(test.lang_1, test.lang_2)) |
Sort Method: quicksort Memory: 25kB |
Buffers: shared hit=1 |
-> Seq Scan on public.test (cost=0.00..40.60 rows=2040 width=12) (actual time=0.006..0.008 rows=17 loops=1) |
Output: LEAST(test.lang_1, test.lang_2), GREATEST(test.lang_1, test.lang_2), test.lpo_id |
Buffers: shared hit=1 |
Planning Time: 0.178 ms |
Execution Time: 0.099 ms |