Hi
On Wed, Nov 29, 2017 at 8:55 AM, Emanuel Alvarez <ema@abductedcow.com.ar> wrote:
> on the other hand, if we disable sequential scans (SET enable_seqscan
> = 0), we see than not only the query runs faster but the cost seems to
> be lower, as seen in the query plan [2].
True, the cost of the scan itself is lower, but together with
hashjoin/nestloop, the total cost of plan [2] is higher.
This is a wild guess but...
-> Index Scan using keywords_pkey on keywords Buffers: shared hit=284808 read=4093
vs
-> Seq Scan on keywords Buffers: shared read=36075
Looks like the index scan's advantage in this example is a much higher
cache hit ratio (despite touching so many more pages) and PostgreSQL
is underestimating it.
Have you tuned the effective_cache_size setting? A good starting point
is half the total RAM in your machine. It would be interesting to see
how high you need to set it for the planner to switch to the index
scan plan.
Regards,
Marti Raudsepp