Indexes are being redone as per these insights. Appreciate the great support.
Thank you
From: Matthew Hall
Sent: Tuesday, June 5, 2018 10:42 AM
To: Fred Habash
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan
Probably the cardinality of "vclf_number" is really bad. So the scan on that
index is returning many million or billion rows and then you get a recheck
which takes semi-forever. So you need an index on cl_value or both vclf_number
and cl_value. If you know some properties of the values actually
Fred Habash writes:
> Indexes:
> "cl_pk" PRIMARY KEY, btree (cl_id)
> "cl_cnst_uk01" UNIQUE CONSTRAINT, btree (cit_id, vclf_number, cl_value)
> "cl_indx_fk01" btree (cit_id)
> "cl_indx_fk02" btree (vclf_number)
This is pretty inefficient index design. Your query is slow b
Hello
Try using index btree(vclf_number, cl_value) instead of btree (vclf_number).
regards, Sergei
Trying to optimize the Elapsed Time (ET) of this query. Currently, it is
hovering around 3 hrs.
Running a 'vaccum analyse' had no effect on ET. Even forcing an
'indexonly' scan by disabling 'enable_seqscan', still around the 3 hrs.
The table is around 4.6B rows,
explain select cit_id, cl_va
