Re: Query taking seq scan on a table

On Sun., Sep. 20, 2020, 10:48 a.m. Shrikant Bhende, <
shrikantpostgresql(at)gmail(dot)com> wrote:

> Hello,
>
> show seq_page_cost; 1
>
> show random_page_cost; 4
>
Set this to 1 and rerun the explain analyze and let us know what you see.
You are using an SSD and the value of 4 should not be apt.

>
> storage type : SSD
>
> Table size : 39 GB
>
> There are no columns added, also I haven't found anything where we need to
> update the data for older rows.
>
> Thanks
>
> On Sun, Sep 20, 2020 at 6:55 PM Avinash Kumar <avinash(dot)vallarapu(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Sun., Sep. 20, 2020, 10:19 a.m. Shrikant Bhende, <
>> shrikantpostgresql(at)gmail(dot)com> wrote:
>>
>>> Hello all,
>>>
>>> I am facing issues with one of the queries running on* Amazon Aurora
>>> (PG VERSION 9.6)* which is taking more than a minute to complete. As
>>> per the initial investigation I assume that one part of the query is taking
>>> a sequential scan on a table("wldbowner.member") which is consuming most of
>>> the execution time. I tried to use index (Btree and GIN ) as well but none
>>> of them were helping to get the query to run faster. Attached is the query
>>> along with the explain analyze of the same, any help on the same would be
>>> appreciated.
>>>
>>> NOTE : I have done the vacuum on the table already to get rid of bloat
>>> issues.
>>> Reindex and test with more work mem is also helping.
>>>
>> Just wanted to clarify one thing before looking at the query.
>>
>> What is random_page_cost set to ?
>>
>>
>>> Thanks and regards
>>>
>>>
>>>