I'm trying to add query result caching to a PostGIS database that's frequently hit with the same (expensive) spatial queries. Looks like pgpool-II is currently the only well-developed solution.

Testing locally, I found that results are great for small responses. An expensive SELECT COUNT(*) takes 0.08s with the pgpool cache, and 9s without it.

Once I try my spatial queries though (responses as big as 300MB), I see no measurable speed improvement. Pgpool's SHOW POOL_CACHE command tells me the cache is indeed used for my query:

-[ RECORD 1 ]---------------+-----------
num_cache_hits              | 1
num_selects                 | 1
cache_hit_ratio             | 0.50
num_hash_entries            | 1048576
used_hash_entries           | 1
num_cache_entries           | 1
used_cache_entries_size     | 4523460
free_cache_entries_size     | 2142960192
fragment_cache_entries_size | 0

Here's my pgpool cache config:

memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_total_size = 2151677952
memqcache_max_num_cache = 1000000
memqcache_maxcache = 536870912
memqcache_cache_block_size = 536870913

Am I doing something wrong, or is this pgpool's cache implementation? Also open to other ways to add server-side query result caching.

EDIT: for the record, here's the test query:

SELECT way, 
FROM   planet_osm_line 
WHERE  ( "highway" IN ( 'motorway', 'motorway_link', 'primary', 'primary_link', 
                        'secondary', 'secondary_link', 'trunk', 'trunk_link', 
                        'tertiary', 'tertiary_link', 'residential', 
                                 'pedestrian' ) 
          OR "route" IN ( 'light_rail', 'railway', 'tracks', 'train' ) ) 
  • Doing only two tries makes an awful bad benchmark, but in your case I suspect that the loop working on the result set and the transmission takes a large part of the time compared to the query time. – eckes Dec 23 '18 at 12:28
  • In this case I think two tries (one hitting the DB and caching the result, the other returning the result from cache) is a reasonable test. These queries run for 20+ seconds, so if the second one isn't way faster something is wrong. Just fetching a cached result and transmitting it shouldn't be taking that long, it's a local connection. – kontextify Dec 23 '18 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.