Search Postgresql Archives

Re: seq vs index scan in join query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi

On Wed, Nov 29, 2017 at 8:55 AM, Emanuel Alvarez <ema@xxxxxxxxxxxxxxxxxx> 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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux