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?.
CREATE TABLE
INSERT 0 5
version | col_a | col_b | col_c |
---|---|---|---|
1 | A1 | B1 | null |
2 | A2 | B2 | null |
3 | A3 | B3 | null |
4 | A5 | null | C1 |
5 | A1 | null | null |
SELECT 5
col_a | col_b | col_c |
---|---|---|
A1 | B3 | C1 |
SELECT 1
col_a | col_b | col_c |
---|---|---|
A1 | B3 | C1 |
SELECT 1
lasta | lastb | lastc |
---|---|---|
A1 | B3 | C1 |
SELECT 1
col_a | col_b | col_c |
---|---|---|
A1 | B3 | C1 |
SELECT 1
CREATE FUNCTION
col_a | col_b | col_c |
---|---|---|
A1 | B3 | C1 |
SELECT 1
CREATE FUNCTION
_col_a | _col_b | _col_c |
---|---|---|
A1 | B3 | C1 |
SELECT 1
CREATE FUNCTION
CREATE AGGREGATE
col_a | col_b | col_c |
---|---|---|
A1 | B3 | C1 |
SELECT 1
INSERT 0 995
ALTER TABLE
VACUUM
QUERY PLAN |
---|
Result (cost=1.40..1.41 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.28..0.71 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.28..43.27 rows=98 width=6) (actual rows=1 loops=1) |
Filter: (col_a IS NOT NULL) |
Rows Removed by Filter: 11 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.28..0.36 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_1 (cost=0.28..43.27 rows=486 width=6) (actual rows=1 loops=1) |
Filter: (col_b IS NOT NULL) |
Rows Removed by Filter: 1 |
InitPlan 3 (returns $2) |
-> Limit (cost=0.28..0.32 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.28..43.27 rows=893 width=6) (actual rows=1 loops=1) |
Filter: (col_c IS NOT NULL) |
Planning Time: 0.309 ms |
Execution Time: 0.111 ms |
EXPLAIN
QUERY PLAN |
---|
Nested Loop (cost=1.10..25.28 rows=1 width=6) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.83..16.98 rows=1 width=8) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.55..8.67 rows=1 width=10) (actual rows=1 loops=1) |
-> Limit (cost=0.28..0.35 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=0.28..73.28 rows=985 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 11 |
-> WindowAgg (cost=0.28..63.27 rows=1000 width=112) (actual rows=12 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.28..43.27 rows=1000 width=10) (actual rows=13 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.28..8.29 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.28..8.29 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.28..8.29 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.266 ms |
Execution Time: 0.205 ms |
EXPLAIN
QUERY PLAN |
---|
HashAggregate (cost=224.49..225.52 rows=103 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
Batches: 1 Memory Usage: 32kB |
-> WindowAgg (cost=199.49..216.99 rows=1000 width=108) (actual rows=1000 loops=1) |
-> Sort (cost=199.49..201.99 rows=1000 width=82) (actual rows=1000 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 103kB |
-> WindowAgg (cost=132.16..149.66 rows=1000 width=82) (actual rows=1000 loops=1) |
-> Sort (cost=132.16..134.66 rows=1000 width=50) (actual rows=1000 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 101kB |
-> WindowAgg (cost=64.83..82.33 rows=1000 width=50) (actual rows=1000 loops=1) |
-> Sort (cost=64.83..67.33 rows=1000 width=18) (actual rows=1000 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 86kB |
-> Seq Scan on tab (cost=0.00..15.00 rows=1000 width=18) (actual rows=1000 loops=1) |
Planning Time: 0.085 ms |
Execution Time: 2.873 ms |
EXPLAIN
QUERY PLAN |
---|
Limit (cost=199.49..199.51 rows=1 width=96) (actual rows=1 loops=1) |
-> Subquery Scan on sub (cost=199.49..226.99 rows=1000 width=96) (actual rows=1 loops=1) |
-> WindowAgg (cost=199.49..216.99 rows=1000 width=108) (actual rows=1 loops=1) |
-> Sort (cost=199.49..201.99 rows=1000 width=82) (actual rows=2 loops=1) |
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 103kB |
-> WindowAgg (cost=132.16..149.66 rows=1000 width=82) (actual rows=1000 loops=1) |
-> Sort (cost=132.16..134.66 rows=1000 width=50) (actual rows=1000 loops=1) |
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 101kB |
-> WindowAgg (cost=64.83..82.33 rows=1000 width=50) (actual rows=1000 loops=1) |
-> Sort (cost=64.83..67.33 rows=1000 width=18) (actual rows=1000 loops=1) |
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 86kB |
-> Seq Scan on tab (cost=0.00..15.00 rows=1000 width=18) (actual rows=1000 loops=1) |
Planning Time: 0.080 ms |
Execution Time: 1.842 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on test_get_lnn (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1) |
Planning Time: 0.016 ms |
Execution Time: 0.187 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1) |
Planning Time: 0.013 ms |
Execution Time: 0.127 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=803.27..803.28 rows=1 width=96) (actual time=2.686..2.686 rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.28..43.27 rows=1000 width=10) (actual time=0.009..0.446 rows=1000 loops=1) |
Planning Time: 0.087 ms |
Execution Time: 2.709 ms |
EXPLAIN
INSERT 0 19000
VACUUM
QUERY PLAN |
---|
Result (cost=9.16..9.17 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.29..6.61 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=98 width=6) (actual rows=1 loops=1) |
Filter: (col_a IS NOT NULL) |
Rows Removed by Filter: 19011 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.29..1.56 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_1 (cost=0.29..620.29 rows=486 width=6) (actual rows=1 loops=1) |
Filter: (col_b IS NOT NULL) |
Rows Removed by Filter: 19001 |
InitPlan 3 (returns $2) |
-> Limit (cost=0.29..0.98 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab tab_2 (cost=0.29..620.29 rows=893 width=6) (actual rows=1 loops=1) |
Filter: (col_c IS NOT NULL) |
Rows Removed by Filter: 19000 |
Planning Time: 0.312 ms |
Execution Time: 8.058 ms |
EXPLAIN
QUERY PLAN |
---|
Nested Loop (cost=1.15..25.28 rows=1 width=6) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.86..16.97 rows=1 width=8) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.57..8.67 rows=1 width=10) (actual rows=1 loops=1) |
-> Limit (cost=0.29..0.35 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=0.29..1220.29 rows=19701 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 19011 |
-> WindowAgg (cost=0.29..1020.29 rows=20000 width=112) (actual rows=19012 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual rows=19013 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.292 ms |
Execution Time: 24.826 ms |
EXPLAIN
QUERY PLAN |
---|
HashAggregate (cost=5775.31..5795.31 rows=2000 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
Batches: 1 Memory Usage: 121kB |
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1) |
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=82) (actual rows=20000 loops=1) |
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1736kB |
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1) |
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1721kB |
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=18) (actual rows=20000 loops=1) |
Planning Time: 0.130 ms |
Execution Time: 158.300 ms |
EXPLAIN
QUERY PLAN |
---|
Limit (cost=5275.31..5275.34 rows=1 width=96) (actual rows=1 loops=1) |
-> Subquery Scan on sub (cost=5275.31..5825.31 rows=20000 width=96) (actual rows=1 loops=1) |
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=1 loops=1) |
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (actual rows=2 loops=1) |
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=82) (actual rows=20000 loops=1) |
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 1736kB |
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1) |
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 1721kB |
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=18) (actual rows=20000 loops=1) |
Planning Time: 0.146 ms |
Execution Time: 71.530 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on test_get_lnn (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1) |
Planning Time: 0.038 ms |
Execution Time: 27.934 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1) |
Planning Time: 0.037 ms |
Execution Time: 23.884 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=15820.29..15820.30 rows=1 width=96) (actual time=8.104..8.105 rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual time=0.014..4.211 rows=20000 loops=1) |
Planning Time: 0.101 ms |
Execution Time: 8.135 ms |
EXPLAIN
CREATE INDEX
CREATE INDEX
CREATE INDEX
VACUUM
QUERY PLAN |
---|
Result (cost=0.87..0.88 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.14..0.24 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_a on tab (cost=0.14..9.61 rows=98 width=6) (actual rows=1 loops=1) |
Heap Fetches: 0 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.27..0.32 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_b on tab tab_1 (cost=0.27..23.56 rows=486 width=6) (actual rows=1 loops=1) |
Heap Fetches: 0 |
InitPlan 3 (returns $2) |
-> Limit (cost=0.28..0.31 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.28..33.67 rows=893 width=6) (actual rows=1 loops=1) |
Heap Fetches: 0 |
Planning Time: 0.383 ms |
Execution Time: 0.313 ms |
EXPLAIN
QUERY PLAN |
---|
Nested Loop (cost=1.15..25.28 rows=1 width=6) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.86..16.97 rows=1 width=8) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.57..8.67 rows=1 width=10) (actual rows=1 loops=1) |
-> Limit (cost=0.29..0.35 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=0.29..1220.29 rows=19701 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 19011 |
-> WindowAgg (cost=0.29..1020.29 rows=20000 width=112) (actual rows=19012 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual rows=19013 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.316 ms |
Execution Time: 25.442 ms |
EXPLAIN
QUERY PLAN |
---|
HashAggregate (cost=5775.31..5795.31 rows=2000 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
Batches: 1 Memory Usage: 121kB |
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=20000 loops=1) |
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=82) (actual rows=20000 loops=1) |
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1736kB |
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1) |
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1721kB |
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=18) (actual rows=20000 loops=1) |
Planning Time: 0.130 ms |
Execution Time: 110.395 ms |
EXPLAIN
QUERY PLAN |
---|
Limit (cost=5275.31..5275.34 rows=1 width=96) (actual rows=1 loops=1) |
-> Subquery Scan on sub (cost=5275.31..5825.31 rows=20000 width=96) (actual rows=1 loops=1) |
-> WindowAgg (cost=5275.31..5625.31 rows=20000 width=108) (actual rows=1 loops=1) |
-> Sort (cost=5275.31..5325.31 rows=20000 width=82) (actual rows=2 loops=1) |
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3496.54..3846.54 rows=20000 width=82) (actual rows=20000 loops=1) |
-> Sort (cost=3496.54..3546.54 rows=20000 width=50) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 1736kB |
-> WindowAgg (cost=1717.77..2067.77 rows=20000 width=50) (actual rows=20000 loops=1) |
-> Sort (cost=1717.77..1767.77 rows=20000 width=18) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 1721kB |
-> Seq Scan on tab (cost=0.00..289.00 rows=20000 width=18) (actual rows=20000 loops=1) |
Planning Time: 0.135 ms |
Execution Time: 71.359 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on test_get_lnn (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1) |
Planning Time: 0.039 ms |
Execution Time: 28.811 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1) |
Planning Time: 0.038 ms |
Execution Time: 24.779 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=15820.29..15820.30 rows=1 width=96) (actual time=7.957..7.957 rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..620.29 rows=20000 width=10) (actual time=0.013..4.083 rows=20000 loops=1) |
Planning Time: 0.116 ms |
Execution Time: 7.985 ms |
EXPLAIN
UPDATE 101
VACUUM
QUERY PLAN |
---|
Result (cost=0.86..0.87 rows=1 width=96) (actual rows=1 loops=1) |
InitPlan 1 (returns $0) |
-> Limit (cost=0.14..0.23 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_a on tab (cost=0.14..9.76 rows=108 width=6) (actual rows=1 loops=1) |
Heap Fetches: 0 |
InitPlan 2 (returns $1) |
-> Limit (cost=0.28..0.32 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_b on tab tab_1 (cost=0.28..24.33 rows=537 width=6) (actual rows=1 loops=1) |
Heap Fetches: 0 |
InitPlan 3 (returns $2) |
-> Limit (cost=0.28..0.31 rows=1 width=6) (actual rows=1 loops=1) |
-> Index Only Scan Backward using ix_version_include_c on tab tab_2 (cost=0.28..35.04 rows=984 width=6) (actual rows=1 loops=1) |
Heap Fetches: 0 |
Planning Time: 0.387 ms |
Execution Time: 0.078 ms |
EXPLAIN
QUERY PLAN |
---|
Nested Loop (cost=1.15..25.28 rows=1 width=6) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.86..16.97 rows=1 width=8) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.57..8.67 rows=1 width=10) (actual rows=1 loops=1) |
-> Limit (cost=0.29..0.35 rows=1 width=16) (actual rows=1 loops=1) |
-> Subquery Scan on q (cost=0.29..1221.29 rows=19701 width=16) (actual rows=1 loops=1) |
Filter: ((q.last_a IS NOT NULL) AND (q.last_b IS NOT NULL) AND (q.last_c IS NOT NULL)) |
Rows Removed by Filter: 3 |
-> WindowAgg (cost=0.29..1021.29 rows=20000 width=112) (actual rows=4 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..621.29 rows=20000 width=10) (actual rows=5 loops=1) |
-> Index Scan using tab_pkey on tab a (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_a) |
-> Index Scan using tab_pkey on tab b (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_b) |
-> Index Scan using tab_pkey on tab c (cost=0.29..8.30 rows=1 width=6) (actual rows=1 loops=1) |
Index Cond: (version = q.last_c) |
Planning Time: 0.288 ms |
Execution Time: 0.080 ms |
EXPLAIN
QUERY PLAN |
---|
HashAggregate (cost=5776.31..5796.31 rows=2000 width=108) (actual rows=1 loops=1) |
Group Key: first_value(col_a) OVER (?), (first_value(col_b) OVER (?)), (first_value(col_c) OVER (?)) |
Batches: 1 Memory Usage: 121kB |
-> WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=20000 loops=1) |
-> Sort (cost=5276.31..5326.31 rows=20000 width=82) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_a IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3497.54..3847.54 rows=20000 width=82) (actual rows=20000 loops=1) |
-> Sort (cost=3497.54..3547.54 rows=20000 width=50) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_b IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1739kB |
-> WindowAgg (cost=1718.77..2068.77 rows=20000 width=50) (actual rows=20000 loops=1) |
-> Sort (cost=1718.77..1768.77 rows=20000 width=18) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (col_c IS NULL) THEN 0 ELSE version END) DESC |
Sort Method: quicksort Memory: 1722kB |
-> Seq Scan on tab (cost=0.00..290.00 rows=20000 width=18) (actual rows=20000 loops=1) |
Planning Time: 0.109 ms |
Execution Time: 114.839 ms |
EXPLAIN
QUERY PLAN |
---|
Limit (cost=5276.31..5276.34 rows=1 width=96) (actual rows=1 loops=1) |
-> Subquery Scan on sub (cost=5276.31..5826.31 rows=20000 width=96) (actual rows=1 loops=1) |
-> WindowAgg (cost=5276.31..5626.31 rows=20000 width=108) (actual rows=1 loops=1) |
-> Sort (cost=5276.31..5326.31 rows=20000 width=82) (actual rows=2 loops=1) |
Sort Key: (CASE WHEN (tab.col_a IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 2331kB |
-> WindowAgg (cost=3497.54..3847.54 rows=20000 width=82) (actual rows=20000 loops=1) |
-> Sort (cost=3497.54..3547.54 rows=20000 width=50) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (tab.col_b IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 1739kB |
-> WindowAgg (cost=1718.77..2068.77 rows=20000 width=50) (actual rows=20000 loops=1) |
-> Sort (cost=1718.77..1768.77 rows=20000 width=18) (actual rows=20000 loops=1) |
Sort Key: (CASE WHEN (tab.col_c IS NULL) THEN 0 ELSE tab.version END) DESC |
Sort Method: quicksort Memory: 1722kB |
-> Seq Scan on tab (cost=0.00..290.00 rows=20000 width=18) (actual rows=20000 loops=1) |
Planning Time: 0.147 ms |
Execution Time: 75.654 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on test_get_lnn (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1) |
Planning Time: 0.040 ms |
Execution Time: 0.228 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1) |
Planning Time: 0.014 ms |
Execution Time: 0.137 ms |
EXPLAIN
QUERY PLAN |
---|
Aggregate (cost=15821.29..15821.30 rows=1 width=96) (actual time=8.622..8.623 rows=1 loops=1) |
-> Index Scan Backward using tab_pkey on tab (cost=0.29..621.29 rows=20000 width=10) (actual time=0.009..4.434 rows=20000 loops=1) |
Planning Time: 0.073 ms |
Execution Time: 8.648 ms |
EXPLAIN
func | test_get_lnn |
---|---|
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
(64,97,18) | (64,97,18) |
SELECT 12
QUERY PLAN |
---|
Function Scan on test_get_lnn (cost=0.25..10.25 rows=1000 width=96) (actual rows=1 loops=1) |
Planning Time: 0.021 ms |
Execution Time: 0.062 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on func (cost=0.25..0.26 rows=1 width=96) (actual rows=1 loops=1) |
Planning Time: 0.014 ms |
Execution Time: 0.057 ms |
EXPLAIN