Gaetano,
> QUERY PLAN
> Hash Join (cost=265.64..32000.76 rows=40612 width=263) (actual
> time=11074.21..11134.28 rows=10 loops=1)
> Hash Cond: ("outer".id_user = "inner".id_user)
> -> Seq Scan on user_logs ul (cost=0.00..24932.65 rows=1258965 width=48)
> (actual time=0.02..8530.21 rows=1258966 loops=1)
OK, here's your problem
The planner thinks that you're going to get 40162 rows out of the final join,
not 10. If the row estimate was correct, then the Seq Scan would be a
reasonable plan. But it's not. Here's some steps you can take to clear
things up for the planner:
1) Make sure you've VACUUM ANALYZED
2) Adjust the following postgresql.conf statistics:
a) effective_cache_size: increase to 70% of available (not used by other
processes) RAM.
b) random_page_cost: decrease, maybe to 2.
c) default_statistics_target: try increasing to 100
(warning: this will significantly increase the time required to do ANALYZE)
Then test again!
--
-Josh Berkus
Aglio Database Solutions
San Francisco
