Re: Zero throughput on a query on a very large table. - Mailing list pgsql-performance
From | ldh@laurent-hasson.com |
---|---|
Subject | Re: Zero throughput on a query on a very large table. |
Date | |
Msg-id | BN6PR15MB11854E833B81D6FA59434844859B0@BN6PR15MB1185.namprd15.prod.outlook.com Whole thread Raw |
In response to | Re: Zero throughput on a query on a very large table. ("ldh@laurent-hasson.com" <ldh@laurent-hasson.com>) |
List | pgsql-performance |
Just a correction from my previous message regarding the throughput we get.
On that one table with 1.2B row, the plan through the index scan delivers actually 50K rows/s in read speed to the application, almost immediately. It would go through the entire table in under 7h vs the other approach which still didn't deliver any data after 10h.
We do additional joins and logic and out final throughput is about 12K/s (what i quoted previously), but this is a case where clearly the index_scan plan delivers vastly better performance than the table_seq_scan+sort plan.
Any insight here?
Thank you,
Laurent.
Sent: Friday, January 25, 2019 2:06:54 PM
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: Zero throughput on a query on a very large table.
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
Is there something that is happening that is causing those settings to not stick? I then tried:
select current_setting('random_page_cost'); --> 0.000001 !!!!
So i think we just spent 4 days on that issue. I then did
And the plan is now using an index scan, and we are getting 12K rows/s in throughput immediately!!! 😊
Thanks,
Laurent.
Sent: Friday, January 25, 2019 1:36:21 PM
To: Tom Lane
Cc: pgsql-performance@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.
Sent: Friday, January 25, 2019 1:34:01 PM
To: ldh@laurent-hasson.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Zero throughput on a query on a very large table.
> 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
pgsql-performance by date: