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, highway, route FROM planet_osm_line WHERE ( "highway" IN ( 'motorway', 'motorway_link', 'primary', 'primary_link', 'secondary', 'secondary_link', 'trunk', 'trunk_link', 'tertiary', 'tertiary_link', 'residential', 'unclassified', 'pedestrian' ) OR "route" IN ( 'light_rail', 'railway', 'tracks', 'train' ) )