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 EXTENSION
CREATE TABLE
| setseed |
|---|
SELECT 1
INSERT 0 100000
INSERT 0 5
DROP INDEX
CREATE INDEX
| id | business_name | x | y | z | t | geom |
|---|---|---|---|---|---|---|
| 1 | business_1 | 0 | 0 | -17.22690410337 | -49.3658619161526 | 01010000E0E610000000000000000000000000000000000000674D2763163A31C0195D3290D4AE48C0 |
| 2 | business_2 | 0 | 0 | 30.0525388042646 | -40.340354600701 | 01010000E0E6100000000000000000000000000000000000006016DE2E730D3E40E78653BD902B44C0 |
| 3 | business_3 | 0 | 0 | -1.6998156895498 | -29.1305271004889 | 01010000E0E6100000000000000000000000000000000000005A81BDEF7132FBBF6FD75B396A213DC0 |
| 4 | business_4 | 0 | 0 | -3.1550972556847 | 1.69759871346233 | 01010000E0E610000000000000000000000000000000000000CDEB46A1A33D09C08FD6C0445D29FB3F |
| 5 | business_5 | 0 | 0 | 35.4744347445728 | 30.7788939904869 | 01010000E0E6100000000000000000000000000000000000005D031847BABC41403231B89865C73E40 |
SELECT 5
VACUUM
PREPARE
| QUERY PLAN |
|---|
| Limit (cost=4402.51..4403.31 rows=7 width=56) (actual time=111.255..114.957 rows=7 loops=1) |
| InitPlan 1 (returns $0) |
| -> Index Scan using business_pkey on business business_1 (cost=0.29..8.31 rows=1 width=48) (actual time=0.008..0.009 rows=1 loops=1) |
| Index Cond: (id = 42) |
| -> Gather Merge (cost=4394.20..11159.19 rows=58826 width=56) (actual time=111.253..114.952 rows=7 loops=1) |
| Workers Planned: 1 |
| Params Evaluated: $0 |
| Workers Launched: 1 |
| -> Sort (cost=3394.19..3541.25 rows=58826 width=56) (actual time=92.673..92.674 rows=6 loops=2) |
| Sort Key: (($0 <<->> business.geom)) |
| Sort Method: top-N heapsort Memory: 26kB |
| Worker 0: Sort Method: top-N heapsort Memory: 26kB |
| -> Parallel Seq Scan on business (cost=0.00..2274.33 rows=58826 width=56) (actual time=0.040..64.524 rows=50002 loops=2) |
| Planning Time: 0.319 ms |
| Execution Time: 115.035 ms |
EXPLAIN
| id | business_name | x | y | z | t | distance |
|---|---|---|---|---|---|---|
| 42 | business_42 | 0 | 0 | -10.0196083837411 | -48.2957109901822 | 0 |
| 31487 | business_31487 | 0 | 0 | -10.1198442476916 | -48.4923364963374 | 0.2207007432989113 |
| 9974 | business_9974 | 0 | 0 | -10.3616938756527 | -48.3508951457488 | 0.34650797220554724 |
| 29527 | business_29527 | 0 | 0 | -10.5012907098008 | -48.2755958509281 | 0.4821021489948953 |
| 2460 | business_2460 | 0 | 0 | -9.75377147442544 | -48.7981151349026 | 0.5684005515362737 |
| 85808 | business_85808 | 0 | 0 | -9.63944638365557 | -48.7459169700765 | 0.589244067124664 |
| 66958 | business_66958 | 0 | 0 | -10.0070484788601 | -48.9255841764578 | 0.6299983984103381 |
SELECT 7
PREPARE
| QUERY PLAN |
|---|
| Limit (cost=4394.20..4395.00 rows=7 width=56) (actual time=104.300..109.837 rows=7 loops=1) |
| -> Gather Merge (cost=4394.20..11159.19 rows=58826 width=56) (actual time=104.299..109.833 rows=7 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Sort (cost=3394.19..3541.25 rows=58826 width=56) (actual time=86.800..86.802 rows=6 loops=2) |
| Sort Key: (('01010000A0E6100000000000000000000000000000000000000000000000000000'::geometry(PointZ,4326) <<->> business.geom)) |
| Sort Method: top-N heapsort Memory: 26kB |
| Worker 0: Sort Method: top-N heapsort Memory: 26kB |
| -> Parallel Seq Scan on business (cost=0.00..2274.33 rows=58826 width=56) (actual time=0.025..59.388 rows=50002 loops=2) |
| Planning Time: 0.110 ms |
| Execution Time: 109.865 ms |
EXPLAIN
| id | business_name | x | y | z | t | distance |
|---|---|---|---|---|---|---|
| 100005 | business_d4 | 0 | 0 | 0 | 1 | 0 |
| 100001 | business_d0 | 0 | 0 | 0 | 0 | 0 |
| 67061 | business_67061 | 0 | 0 | -0.000232699105652046 | -39.8002922999848 | 0.000232699105652046 |
| 65392 | business_65392 | 0 | 0 | -0.0019007474728383 | 38.913986390566 | 0.0019007474728383 |
| 8053 | business_8053 | 0 | 0 | 0.0024517452727153 | 18.420606497608 | 0.0024517452727153 |
| 46210 | business_46210 | 0 | 0 | 0.00258020904213652 | 46.3335437918857 | 0.00258020904213652 |
| 45040 | business_45040 | 0 | 0 | 0.0034545808726989 | 44.9821911055047 | 0.0034545808726989 |
SELECT 7
PREPARE
| QUERY PLAN |
|---|
| Limit (cost=4394.20..4395.00 rows=7 width=56) (actual time=130.547..131.964 rows=7 loops=1) |
| -> Gather Merge (cost=4394.20..11159.19 rows=58826 width=56) (actual time=130.546..131.961 rows=7 loops=1) |
| Workers Planned: 1 |
| Workers Launched: 1 |
| -> Sort (cost=3394.19..3541.25 rows=58826 width=56) (actual time=91.872..91.873 rows=6 loops=2) |
| Sort Key: (('01010000E0E61000000000000000000000000000000000000000000000000000000000000000000000'::geometry(PointZM,4326) <<->> business.geom)) |
| Sort Method: top-N heapsort Memory: 26kB |
| Worker 0: Sort Method: top-N heapsort Memory: 26kB |
| -> Parallel Seq Scan on business (cost=0.00..2274.33 rows=58826 width=56) (actual time=0.027..65.154 rows=50002 loops=2) |
| Planning Time: 0.104 ms |
| Execution Time: 131.986 ms |
EXPLAIN
| id | business_name | x | y | z | t | distance |
|---|---|---|---|---|---|---|
| 100001 | business_d0 | 0 | 0 | 0 | 0 | 0 |
| 18049 | business_18049 | 0 | 0 | 0.032591229985421 | -0.0790162422833731 | 0.08547370833506245 |
| 39427 | business_39427 | 0 | 0 | -0.152780772661544 | 0.153586720785384 | 0.2166352817448393 |
| 59583 | business_59583 | 0 | 0 | -0.0175589541282477 | 0.241464075804387 | 0.24210166619448256 |
| 3802 | business_3802 | 0 | 0 | -0.0101400731103567 | -0.269685873945576 | 0.2698764378164061 |
| 82379 | business_82379 | 0 | 0 | -0.232590562265635 | 0.137020505273753 | 0.26994997410727567 |
| 2607 | business_2607 | 0 | 0 | 0.0952342343135442 | -0.257663234646735 | 0.27469965757879183 |
SELECT 7
PREPARE
| QUERY PLAN |
|---|
| Limit (cost=0.00..2789.07 rows=1 width=56) (actual time=0.032..0.050 rows=7 loops=1) |
| -> Seq Scan on business (cost=0.00..2789.07 rows=1 width=56) (actual time=0.031..0.048 rows=7 loops=1) |
| Filter: (geom &&& '01030000A0E6100000010000000B000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002240000000000000000000000000000022400000000000002240000000000000000000000000000022400000000000000000000000000000000000000000000000000000000000000000000000000000224000000000000000000000000000000000000000000000224000000000000000000000000000002240000000000000224000000000000022400000000000002240000000000000224000000000000022400000000000000000000000000000224000000000000000000000000000000000000000000000000000000000000000000000000000000000'::geometry) |
| Rows Removed by Filter: 52 |
| Planning Time: 0.804 ms |
| Execution Time: 0.077 ms |
EXPLAIN
| id | business_name | x | y | z | t | dist |
|---|---|---|---|---|---|---|
| 20 | business_20 | 0 | 0 | 1.62089466211279 | 19.4088238958816 | 2.220446049250313e-16 |
| 39 | business_39 | 0 | 0 | 7.98920478768979 | -46.7405163499983 | 0 |
| 41 | business_41 | 0 | 0 | 4.48607660877774 | 49.0617357165086 | 0 |
| 43 | business_43 | 0 | 0 | 0.328865345120398 | 25.2831024125602 | 0 |
| 51 | business_51 | 0 | 0 | 4.44946327014299 | -35.3005902684503 | 0 |
| 53 | business_53 | 0 | 0 | 7.08339819228125 | -12.0444142467781 | 0 |
| 59 | business_59 | 0 | 0 | 1.44919680955346 | -17.203742443276 | 0 |
SELECT 7