1

I only have 12GB of free space. Is there a way to create this table without creating a bunch of temporary files? It runs out of space and never completes. I want to know how many cities are within 80km of each city.

SELECT a.k, count(a) x 
FROM     cities AS a 
JOIN     cities AS b   
  ON     ST_DWithin(a.geog, b.geog, 80000)   
 AND     a.k != b.k 
WHERE    a.l = true 
group by a.k 
;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--
 Finalize GroupAggregate  (cost=1282860.42..1285716.94 rows=24280 width=15)
   Group Key: a.k
   ->  Gather Merge  (cost=1282860.42..1285372.97 rows=20234 width=15)
         Workers Planned: 2
         ->  Partial GroupAggregate  (cost=1281860.39..1282037.44 rows=10117 width=15)
               Group Key: a.k
               ->  Sort  (cost=1281860.39..1281885.69 rows=10117 width=348)
                     Sort Key: a.k
                     ->  Nested Loop  (cost=0.41..1281187.39 rows=10117 width=348)
                           ->  Parallel Seq Scan on cities a  (cost=0.00..11417.42 rows=111968 width=380)
                                 Filter: l
                           ->  Index Scan using citiesn_geog_idx on cities b  (cost=0.41..11.33 rows=1 width=39)
                                 Index Cond: (geog && _st_expand(a.geog, '80000'::double precision))
                                 Filter: ((a.k <> k) AND (a.geog && _st_expand(geog, '80000'::double precision)) AND _st_dwithin(a.geog, geog, '80000'::double precision, true)
)
(14 rows)

when I only limit it to 1000 it is really fast

Limit  (cost=0.83..127479.34 rows=1000 width=15) (actual time=0.858..440.869 rows=1000 loops=1)
   ->  GroupAggregate  (cost=0.83..3095179.08 rows=24280 width=15) (actual time=0.856..440.550 rows=1000 loops=1)
         Group Key: a.k
         ->  Nested Loop  (cost=0.83..3094814.88 rows=24280 width=348) (actual time=0.711..423.748 rows=42526 loops=1)
               ->  Index Scan using citiesk on cities a  (cost=0.42..47380.58 rows=268722 width=380) (actual time=0.027..4.349 rows=1043 loops=1)
                     Filter: l
                     Rows Removed by Filter: 79
               ->  Index Scan using citiesn_geog_idx on cities b  (cost=0.41..11.33 rows=1 width=39) (actual time=0.083..0.389 rows=41 loops=1043)
                     Index Cond: (geog && _st_expand(a.geog, '80000'::double precision))
                     Filter: ((a.k <> k) AND (a.geog && _st_expand(geog, '80000'::double precision)) AND _st_dwithin(a.geog, geog, '80000'::double precision, true))
                     Rows Removed by Filter: 11
 Planning Time: 0.480 ms
 Execution Time: 448.505 ms
(13 rows)

after vacuum analyze and with count(a.k)

GroupAggregate  (cost=3058755.01..3059179.53 rows=24258 width=15)
   Group Key: a.k
   ->  Sort  (cost=3058755.01..3058815.66 rows=24258 width=7)
         Sort Key: a.k
         ->  Nested Loop  (cost=0.41..3056988.28 rows=24258 width=7)
               ->  Seq Scan on cities a  (cost=0.00..12999.33 rows=268413 width=39)
                     Filter: l
               ->  Index Scan using citiesn_geog_idx on cities b  (cost=0.41..11.33 rows=1 width=39)
                     Index Cond: (geog && _st_expand(a.geog, '80000'::double precision))
                     Filter: ((a.k <> k) AND (a.geog && _st_expand(geog, '80000'::double precision)) AND
 _st_dwithin(a.geog, geog, '80000'::double precision, true))
(10 rows)
5
  • 1
    run VACUUM ANALYZE cities to guarantee the planer can work with updated table statistics; with more index use, less size for fetched rows is needed. count only the id column, not the full row (COUNT(b.k)); this reduces the size of the returned rows (estimated as width in bytes per row), for each node in the plan. you might also want to check if you can create an actual table with that query, since much memory overhead can be saved if no output has to be generated...but I'm not 100% sure of the impact here
    – geozelot
    Mar 25, 2019 at 22:46
  • it was able to do it. I think using count(a.k) helped a lot! I'm still interested, however, if there is any other optimizations that I could do on a query just like this because I have similar ones that I want to write so I'll leave this question open for other replies
    – jaksco
    Mar 26, 2019 at 1:18
  • If you have lots of entries with a.l = false, you could try making the index citiesk a partial one on k ( create index citiesk on cities (k) where l;)
    – JGH
    Mar 26, 2019 at 11:20
  • 1
    You can replace a.k != b.k with a.k > b.k which reduces the comparisons from n*(n-1) to n*(n-1)/2, ie, it halves them. Mar 26, 2019 at 12:31
  • Also, as I believe temporary results will have to be written to memory/disk, before group by is called, this ought to reduce your space problems also. Mar 26, 2019 at 14:02

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.