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?.
10 rows affected
id | username | person | tweets | followers | following | likes | created_at |
---|---|---|---|---|---|---|---|
3fa34100-d688-4051-a687-ec49d05e7212 | renok | null | 110 | 6 | 0 | 0 | 2020-10-10 |
bab9ceb9-2770-49ea-8489-77e5d763a223 | Lydia_C | test user2 | 515 | 1301 | 1852 | 1677 | 2020-10-10 |
4649077a-9188-4821-a1ec-3b38608ea44a | Kingston_Sav | null | 2730 | 1087 | 1082 | 1339 | 2020-10-10 |
eef80836-e140-4adc-9598-8b612ab1825b | TP_s | null | 1835 | 998 | 956 | 1832 | 2020-10-10 |
fd3ff8c7-0994-40b6-abe0-915368ab9ae5 | DKSnr4 | null | 580 | 268 | 705 | 703 | 2020-10-10 |
3fa34100-d688-4051-a687-ec49d05e7212 | renok | null | 119 | 6 | 0 | 0 | 2020-10-12 |
bab9ceb9-2770-49ea-8489-77e5d763a223 | Lydia_C | test user2 | 516 | 1304 | 1852 | 1687 | 2020-10-12 |
4649077a-9188-4821-a1ec-3b38608ea44a | Kingston_Sav | null | 2737 | 1090 | 1084 | 1342 | 2020-10-12 |
eef80836-e140-4adc-9598-8b612ab1825b | TP_s | null | 1833 | 1001 | 957 | 1837 | 2020-10-12 |
fd3ff8c7-0994-40b6-abe0-915368ab9ae5 | DKSnr4 | null | 570 | 268 | 700 | 703 | 2020-10-12 |
id | username | person | tweets | followers | following | likes | created_at | prev_followers | gain |
---|---|---|---|---|---|---|---|---|---|
4649077a-9188-4821-a1ec-3b38608ea44a | Kingston_Sav | null | 2737 | 1090 | 1084 | 1342 | 2020-10-12 | 1087 | 3 |
QUERY PLAN |
---|
Limit (cost=51.47..51.47 rows=1 width=212) (actual time=0.074..0.077 rows=3 loops=1) |
CTE cte |
-> WindowAgg (cost=28.89..35.19 rows=360 width=204) (actual time=0.029..0.038 rows=10 loops=1) |
-> Sort (cost=28.89..29.79 rows=360 width=196) (actual time=0.019..0.020 rows=10 loops=1) |
Sort Key: t.created_at DESC |
Sort Method: quicksort Memory: 26kB |
-> Seq Scan on t (cost=0.00..13.60 rows=360 width=196) (actual time=0.007..0.009 rows=10 loops=1) |
-> Sort (cost=16.28..16.29 rows=1 width=212) (actual time=0.073..0.074 rows=4 loops=1) |
Sort Key: ((cte.followers - cte_1.followers)) DESC |
Sort Method: quicksort Memory: 25kB |
-> Nested Loop (cost=0.00..16.27 rows=1 width=212) (actual time=0.052..0.065 rows=5 loops=1) |
Join Filter: ((cte.username)::text = (cte_1.username)::text) |
Rows Removed by Join Filter: 20 |
-> CTE Scan on cte (cost=0.00..8.10 rows=2 width=204) (actual time=0.034..0.036 rows=5 loops=1) |
Filter: (rnk = 1) |
Rows Removed by Filter: 5 |
-> CTE Scan on cte cte_1 (cost=0.00..8.10 rows=2 width=52) (actual time=0.003..0.004 rows=5 loops=5) |
Filter: (rnk = 2) |
Rows Removed by Filter: 5 |
Planning Time: 0.198 ms |
Execution Time: 0.151 ms |
QUERY PLAN |
---|
Limit (cost=51.47..51.47 rows=1 width=212) (actual time=0.116..0.117 rows=1 loops=1) |
CTE cte |
-> WindowAgg (cost=28.89..35.19 rows=360 width=204) (actual time=0.022..0.032 rows=10 loops=1) |
-> Sort (cost=28.89..29.79 rows=360 width=196) (actual time=0.017..0.018 rows=10 loops=1) |
Sort Key: t.created_at DESC |
Sort Method: quicksort Memory: 26kB |
-> Seq Scan on t (cost=0.00..13.60 rows=360 width=196) (actual time=0.008..0.010 rows=10 loops=1) |
-> Sort (cost=16.28..16.29 rows=1 width=212) (actual time=0.115..0.115 rows=1 loops=1) |
Sort Key: ((cte.followers - cte_1.followers)) DESC, cte.followers, cte.username |
Sort Method: top-N heapsort Memory: 25kB |
-> Nested Loop (cost=0.00..16.27 rows=1 width=212) (actual time=0.089..0.102 rows=5 loops=1) |
Join Filter: ((cte.username)::text = (cte_1.username)::text) |
Rows Removed by Join Filter: 20 |
-> CTE Scan on cte (cost=0.00..8.10 rows=2 width=204) (actual time=0.070..0.072 rows=5 loops=1) |
Filter: (rnk = 1) |
Rows Removed by Filter: 5 |
-> CTE Scan on cte cte_1 (cost=0.00..8.10 rows=2 width=52) (actual time=0.003..0.005 rows=5 loops=5) |
Filter: (rnk = 2) |
Rows Removed by Filter: 5 |
Planning Time: 0.152 ms |
Execution Time: 0.146 ms |
QUERY PLAN |
---|
Subquery Scan on cte3 (cost=33.33..33.45 rows=1 width=212) (actual time=0.117..0.123 rows=3 loops=1) |
Filter: (cte3.rnk = 1) |
Rows Removed by Filter: 2 |
-> WindowAgg (cost=33.33..33.40 rows=4 width=212) (actual time=0.116..0.122 rows=5 loops=1) |
-> Sort (cost=33.33..33.34 rows=4 width=204) (actual time=0.113..0.115 rows=5 loops=1) |
Sort Key: cte2.gain DESC |
Sort Method: quicksort Memory: 25kB |
-> Subquery Scan on cte2 (cost=33.16..33.29 rows=4 width=204) (actual time=0.095..0.108 rows=5 loops=1) |
Filter: (cte2.gain IS NOT NULL) |
Rows Removed by Filter: 5 |
-> WindowAgg (cost=33.16..33.25 rows=4 width=204) (actual time=0.094..0.105 rows=10 loops=1) |
-> Sort (cost=33.16..33.17 rows=4 width=196) (actual time=0.085..0.087 rows=10 loops=1) |
Sort Key: src.id, src.created_at DESC |
Sort Method: quicksort Memory: 26kB |
-> Hash Join (cost=18.55..33.12 rows=4 width=196) (actual time=0.060..0.066 rows=10 loops=1) |
Hash Cond: (src.created_at = t.created_at) |
-> Seq Scan on t src (cost=0.00..13.60 rows=360 width=196) (actual time=0.007..0.008 rows=10 loops=1) |
-> Hash (cost=18.52..18.52 rows=2 width=4) (actual time=0.035..0.035 rows=2 loops=1) |
Buckets: 1024 Batches: 1 Memory Usage: 9kB |
-> Limit (cost=18.50..18.50 rows=2 width=4) (actual time=0.028..0.030 rows=2 loops=1) |
-> Sort (cost=18.50..19.00 rows=200 width=4) (actual time=0.028..0.028 rows=2 loops=1) |
Sort Key: t.created_at DESC |
Sort Method: quicksort Memory: 25kB |
-> HashAggregate (cost=14.50..16.50 rows=200 width=4) (actual time=0.012..0.014 rows=2 loops=1) |
Group Key: t.created_at |
Batches: 1 Memory Usage: 40kB |
-> Seq Scan on t (cost=0.00..13.60 rows=360 width=4) (actual time=0.004..0.005 rows=10 loops=1) |
Planning Time: 0.183 ms |
Execution Time: 0.210 ms |