It sure turned out that default settings are not a good fit. Setting random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are being used in explain plan and IO utilization is close to 0.
QUERY PLAN
Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual time=2176.045..2418.162 rows=241238 loops=1)
Sort Method: external sort Disk: 4248kB
-> Nested Loop (cost=0.00..950554.81 rows=200575 width=8) (actual time=0.048..1735.357 rows=241238 loops=1)
-> Index Scan using visits_created_at_index on visits (cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..178.591 rows=136021 loops=1)
Index Cond: ((created_at >= '2012-12-15 00:00:00'::timestamp without time zone) AND (created_at < '2012-12-16 00:00:00'::timestamp without time zone))
-> Index Scan using views_visit_id_index on views (cost=0.00..11.33 rows=12 width=8) (actual time=0.004..0.006 rows=2 loops=136021)
Index Cond: (visit_id = visits.id)
Total runtime: 2635.169 ms
However I noticed that sorting is done using disk("external sort Disk: 4248kB") which prompted me to take a look at work_mem. But it turned out that small increase to 4MB from default 1MB turns off index usage and query gets x10 slower. IO utilization jumped to 100% from literally nothing. so back to square one...
QUERY PLAN
Sort (cost=936642.75..937144.19 rows=200575 width=8) (actual time=33200.762..33474.443 rows=241238 loops=1)
Sort Method: external merge Disk: 4248kB
-> Hash Join (cost=6491.17..917410.33 rows=200575 width=8) (actual time=7156.498..32723.221 rows=241238 loops=1)
Hash Cond: (views.visit_id = visits.id)
-> Seq Scan on views (cost=0.00..832189.95 rows=8768395 width=8) (actual time=0.100..12126.342 rows=8200704 loops=1)
-> Hash (cost=5459.16..5459.16 rows=82561 width=4) (actual time=353.683..353.683 rows=136021 loops=1)
Buckets: 16384 Batches: 2 (originally 1) Memory Usage: 4097kB
-> Index Scan using visits_created_at_index on visits (cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..175.051 rows=136021 loops=1)
Index Cond: ((created_at >= '2012-12-15 00:00:00'::timestamp without time zone) AND (created_at < '2012-12-16 00:00:00'::timestamp without time zone))
Total runtime: 33698.000 ms
Basically PG is going through all views again and not using "Index Scan using views_visit_id_index on views". Looks like setting work_mem confuses planner somehow. Any idea what can be done to do sorting in memory. I suspect it should make query even more faster. Thanks -Alex
nothing wrong with that, but keep in mind you can tweak
'effective_cache_size' for a single session with 'set' command;
merlin
