pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Fred Habash <fmhabash@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan
Date: Tue, 5 Jun 2018 10:17:08 -0400
Message-ID: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com> (raw)

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_value from reflink.citation_locators where 
cl_value = '1507617681' and vclf_number = 1 ;
                                        QUERY PLAN
-----------------------------------------------------------------------------------------
  Bitmap Heap Scan on citation_locators (cost=5066559.01..50999084.79 
rows=133 width=23)
    Recheck Cond: (vclf_number = 1)
    Filter: (cl_value = '1507617681'::text)
    ->  Bitmap Index Scan on cl_indx_fk02 (cost=0.00..5066558.97 
rows=493984719 width=0)
          Index Cond: (vclf_number = 1)
(5 rows)

reflink.citation_locators
                                 Table"reflink.citation_locators"
       Column      |           Type           | Modifiers | Storage  | Stats target | Description
------------------+--------------------------+-----------+----------+--------------+-------------
  cl_id            | bigint                   | notnull   | plain    |              |
  cl_value         | text                     | notnull   | extended |              |
  vclf_number      | integer                  | notnull   | plain    |              |
  cit_id           | bigint                   | notnull   | plain    |              |
  cl_date_created  | timestamp with time zone | notnull   | plain    |              |
  cl_date_modified | timestamp with time zone |           | plain    |              |
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)
Foreign-key constraints:
     "cl_cnst_fk01"  FOREIGN KEY (cit_id) REFERENCES citations(cit_id) NOT VALID"cl_cnst_fk02"  FOREIGN KEY (vclf_number) REFERENCES valid_cit_locator_fields(vclf_number)

view thread (5+ messages)  latest in thread

Message-ID: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com>
Permalink:  ../560c24f9-dced-94ae-8e14-fe9451246342@gmail.com/
Also on:    postgresql.org/message-id/560c24f9-dced-94ae-8e14-fe9451246342@gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: fmhabash@gmail.com, pgsql-performance@lists.postgresql.org
  Subject: Re: Simple Query Elapsed Time ~ 3 hrs Using Bitmap Index/Heap Scan
  In-Reply-To: <560c24f9-dced-94ae-8e14-fe9451246342@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox