RE: Zero throughput on a query on a very large table. - Mailing list pgsql-performance

> -----Original Message-----
> From: Andreas Kretschmer <andreas@a-kretschmer.de>
> Sent: Friday, January 25, 2019 00:55
> To: pgsql-performance@lists.postgresql.org
> Subject: Re: Zero throughput on a query on a very large table.
> 
> 
> 
> Am 25.01.19 um 06:20 schrieb ldh@laurent-hasson.com:
> >
> > Hello,
> >
> > We have been stuck for the past week on a query that simply won’t
> > “execute”. We have a table with 1.2B rows that took around 14h to
> > load, but a simple select takes forever and after 10h, no records are
> > coming through still.
> >
> > Environment:
> >
> >      - Table tmp_outpatient_rev with 41 VARCHAR columns
> > (desy_sort_key, claim_no, clm_line_num, clm_thru_dt, nch_clm_type_cd,
> > rev_cntr, rev_cntr_dt, …)
> >
> >      - 1.2B rows (Billion with a ‘B’)
> >
> >      - A single Unique Index on columns desy_sort_key, claim_no,
> > clm_line_num
> >
> >      - select pg_size_pretty(pg_relation_size('tmp_outpatient_rev'))
> > --> 215GB
> >
> >      - Database Server: 64GB, 8 cores/16 threads, HDDs 10K
> >
> >      - Linux
> >
> >      - PG 11.1
> >
> > Query:
> >
> >      select * from tmp_outpatient_rev order by desy_sort_key, claim_no
> >
> > Plan:
> >
> >     Gather Merge (cost=61001461.16..216401602.29 rows=1242732290
> > width=250)
> >
> >       Output: desy_sort_key, claim_no, clm_line_num, clm_thru_dt,
> > nch_clm_type_cd, rev_cntr, rev_cntr_dt, …
> >
> >       Workers Planned: 10
> >
> >       ->  Sort (cost=61000460.97..61311144.04 rows=124273229
> > width=250)
> >
> >             Output: desy_sort_key, claim_no, clm_line_num,
> > clm_thru_dt, nch_clm_type_cd, rev_cntr, rev_cntr_dt, …
> >
> >             Sort Key: tmp_outpatient_rev.desy_sort_key,
> > tmp_outpatient_rev.claim_no
> >
> >             ->  Parallel Seq Scan on public.tmp_outpatient_rev
> > (cost=0.00..29425910.29 rows=124273229 width=250)
> >
> >                   Output: desy_sort_key, claim_no, clm_line_num,
> > clm_thru_dt, nch_clm_type_cd, rev_cntr, rev_cntr_dt, …
> >
> > Method of access:
> >
> >     - Using Pentaho Kettle (an ETL tool written in Java and using
> > JDBC), we simply issue the query and expect records to start streaming
> > in ASAP.
> >
> >    - Issue was replicated with really basic JDBC code in a Java test
> > program.
> >
> >     - The database doesn't have much other data and the table was
> > loaded from a CSV data source with LOAD over something like 14h
> > (average throughput of about 25K rows/s)
> >
> >     - Settings:
> >
> >               alter database "CMS_TMP" set seq_page_cost=1;
> >
> >               alter database "CMS_TMP" set random_page_cost=4;
> >
> >               alter database "CMS_TMP" set enable_seqscan=true;
> >
> >               JDBC connection string with no extra params.
> >
> >               Database has been generally configured properly.
> >
> > Problem:
> >
> >     - The plan shows a full table scan followed by a sort, and then a
> > gather merge. With 1.2B rows, that's crazy to try to sort that 😊
> >
> >     - After 10h, the query is still "silent" and no record is
> > streaming in. IO is very high (80-90% disk throughput utilization) on
> > the machine (the sort…).
> >
> >     - I have tried to hack the planner to force an index scan (which
> > would avoid the sort/gather steps and should start streaming data
> > right away), in particular, enable_seqscan=false or seq_page_cost=2.
> > This had ZERO impact on the plan to my surprise.
> >
> >    - I changed the “order by” to include all 3 columns from the index,
> > or created a non-unique index with only the first 2 columns, all to no
> > effect whatsoever either.
> >
> >     - The table was written over almost 14h at about 25K row/s and it
> > seems to me I should be able to read the data back at least as fast.
> >
> > Why is a simple index scan not used? Why are all our efforts to try to
> > force the use of the index failing?
> >
> >
> 
> the query isn't that simple, there is no where condition, so PG has to read the
> whole table and the index is useless. Would it be enought to select only the
> columns covered by the index?
> (run a vacuum on the table after loading the data, that's can enable a index-
> only-scan in this case)
> 
> 
> 
> 
> Regards, Andreas
> 
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
> 

Well, even without a where clause, and a straight select with an order by on an index... The index may perform slightly
moreslowly, but stream data more rapidly... I guess what i am pointing out is that in ETL scenarios, enabling better
continuousthroughput would be better than total overall query performance?
 

Thank you,
Laurent.