add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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