Re: index only scan taking longer to run

On Thu, 29 Apr 2021 at 21:02, Ayub M <hiayub(at)gmail(dot)com> wrote:
>
> In the below execution plan, the index scan on five_lima (table has 900m records) is where it's spending most of its time. I want to bring down the runtime to a few seconds, how do I optimize it? Tried forcing seq scan and ran vacuum/analyze but it is not helping.

You might want to look into the track_io_timing GUC and EXPLAIN
(ANALYZE, BUFFERS) to get an idea of if the additional time is spent
doing I/O or not.

> As per explain analysis from depesz, the index scan on five_lima is spending 86% of time.

If you don't think the Nested Loop join to five_lima is the best plan,
then you could check if effective_cache_size is set correctly. Too
high a value there could cause more parameterized nested loop joins
than you might like. random_page_cost is also important here. If
that's set too low then the planner might tend prefer nested loops
with index scans more than hash and merge joins.

Check the documents for more details on those settings.

David