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?.
500000 rows affected
500000 rows affected
| id | lon | lat | geog | result |
|---|---|---|---|---|
| 383442 | 2.157323757651163 | 1.056750680871005 | 0101000020E6100000F0F64FF53242014000DEE56673E8F03F | 5314009.69794364 |
| 169865 | 2.2059135283701394 | 1.1016310012425947 | 0101000020E6100000D03AF1FDB5A50140008C29D447A0F13F | 5308667.89182589 |
| 77724 | 1.9530096814634348 | 1.114054602289002 | 0101000020E610000060826A14873FFF3F00A22CEB2AD3F13F | 5309222.15228718 |
| 120266 | 1.9292809414302994 | 1.1028854175609695 | 0101000020E6100000E0D343B155DEFE3F008EFA2D6BA5F13F | 5310647.93417585 |
| 31498 | 1.881548346295169 | 1.0366861255469075 | 0101000020E6100000E0E35270D21AFE3F000ED7304496F03F | 5318368.95472371 |
| 19935 | 1.820633430456219 | 1.0506640998196417 | 0101000020E610000020051D855021FD3F00E8BC2885CFF03F | 5317307.56265526 |
| 340827 | 1.9088592970856624 | 1.139415906924114 | 0101000020E61000002060DA0BB08AFE3F00808C2C0C3BF23F | 5306757.9350283 |
| 234854 | 1.9526507191621292 | 1.2144607547182886 | 0101000020E6100000E0FE34AE0E3EFF3F00A67C666E6EF33F | 5298088.28169443 |
| 401804 | 1.9207004804201446 | 1.2074985815562513 | 0101000020E6100000600E4D6D30BBFE3F00025C08EA51F33F | 5299112.84864884 |
| 404443 | 1.8988553347153427 | 1.2083865864187828 | 0101000020E61000002000A721B661FE3F0080592C8D55F33F | 5299187.90813604 |
| 175605 | 1.7899378564241388 | 1.216962835125969 | 0101000020E6100000A06CB3E095A3FC3F00049505AE78F33F | 5299114.37122336 |
| 332556 | 2.033284226897102 | 1.2592898771837326 | 0101000020E610000030F74F852A44004000026B240D26F43F | 5292486.45689654 |
| 155891 | 2.079902947076228 | 1.3395522358323433 | 0101000020E6100000105F0428A4A3004000F04253CE6EF53F | 5283224.02122415 |
| QUERY PLAN |
|---|
| Limit (cost=16319.64..16646.16 rows=13 width=60) (actual time=962.656..964.813 rows=13 loops=1) |
| -> Gather Merge (cost=16319.64..10481584.07 rows=416666 width=60) (actual time=962.654..964.809 rows=13 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Result (cost=15319.62..5226248.78 rows=208333 width=60) (actual time=861.609..861.663 rows=11 loops=3) |
| -> Sort (cost=15319.62..15840.45 rows=208333 width=52) (actual time=842.118..842.122 rows=11 loops=3) |
| Sort Key: geog |
| Sort Method: top-N heapsort Memory: 28kB |
| Worker 0: Sort Method: top-N heapsort Memory: 28kB |
| Worker 1: Sort Method: top-N heapsort Memory: 27kB |
| -> Parallel Seq Scan on data (cost=0.00..10423.33 rows=208333 width=52) (actual time=4.847..92.551 rows=166667 loops=3) |
| Planning Time: 0.110 ms |
| Execution Time: 964.879 ms |