Re: Zero throughput on a query on a very large table.

[l...@laurent-hasson.com]

OK... I think we may have cracked this.


First, do you think that 128MB work_mem is ok? We have a 64GB machine and 
expecting fewer than 100 connections. This is really an ETL workload 
environment at this time.


Second, here is what i found and what messed us up.

    select current_setting('random_page_cost'); --> 4

    alter database "CMS_TMP" set random_page_cost=0.00000001;
    select current_setting('random_page_cost'); --> 4 ????

I also tried:
    select current_setting('random_page_cost'); --> 4
    select set_config('random_page_cost', '0.000001', true);
    select current_setting('random_page_cost'); --> 4 ????


Is there something that is happening that is causing those settings to not 
stick? I then tried:


    select current_setting('random_page_cost'); --> 4
    select set_config('random_page_cost', '0.000001', false); -- false now, 
i.e., global
    select current_setting('random_page_cost'); --> 0.000001 !!!!


So i think we just spent 4 days on that issue. I then did

    select set_config('enable_seqscan', 'off', false);
And the plan is now using an index scan, and we are getting 12K rows/s in 
throughput immediately!!! 😊

So i guess my final question is that i really want to only affect that one 
query executing, and i seem to not be able to change the settings used by the 
planner just for that one transaction. I have to change it globally which i 
would prefer not to do. Any help here?


Thanks,

Laurent.

________________________________
From: l...@laurent-hasson.com <l...@laurent-hasson.com>
Sent: Friday, January 25, 2019 1:36:21 PM
To: Tom Lane
Cc: pgsql-performa...@postgresql.org
Subject: Re: Zero throughput on a query on a very large table.


Sorry :) When i look at the "SQL" tab in PGAdmin when i select the index in the 
schema browser. But you are right that /d doesn't show that.

________________________________
From: Tom Lane <t...@sss.pgh.pa.us>
Sent: Friday, January 25, 2019 1:34:01 PM
To: l...@laurent-hasson.com
Cc: pgsql-performa...@postgresql.org
Subject: Re: Zero throughput on a query on a very large table.

"l...@laurent-hasson.com" <l...@laurent-hasson.com> writes:
> Also, the original statement i implemented did not have all of that. This is 
> the normalized SQL that Postgres now gives when looking at the indices.

[ squint... ]  What do you mean exactly by "Postgres gives that"?
I don't see any redundant COLLATE clauses in e.g. psql \d.

                        regards, tom lane