How to reduce query time

Question

I have a JSF PrimeFaces DataTable with enabled lazy and pagination option and query to one table in PostgreSQL DB. The table contains 7_000_000 rows.

create table dkp_card_data(
id numeric(10) primary key,
name1 varchar(255),
name2 varchar(255),
name3 varchar(255),
value varchar(3999),
fk_id numeric(10) on update restrict on delete cascade);

create index idx on dkp_card_data (name1, name2, name3, value, fk_id);
create index inx_2 on dkp_card_data (fk_id);

The problem is too long time of loading data from db.

I measured the time of java code and discovered a lot of the time is spent on one query in the Jpa Repository.

This is the method:

@Query(value = "select distinct d.value from Data d where d.name1 = ?1 and d.name2 = ?2 and dcd.name = ?3")
Page<String> findValuesByName1AndName2AndName3WithPage(String name1, String name2, String name3, Pageable pageable);

Hibernate generates the queries and executes them twice:

select distinct d.VALUE as col_0_0_
from DATA d
where d.NAME1=?and d.NAME2=?and d.NAME3=?
order by d.VALUE asc limit ?;

Limit  (cost=0.56..234.51 rows=5 width=9) (actual time=0.054..0.101 rows=5 loops=1)
  ->  Unique  (cost=0.56..164514.90 rows=3516 width=9) (actual time=0.053..0.100 rows=5 loops=1)
        ->  Index Only Scan using idx_constraint_dcdfk_tag_nm on data d  (cost=0.56..163259.98 rows=501966 width=9) (actual time=0.051..0.090 rows=21 loops=1)
              Index Cond: ((name1 = 'common'::text) AND (name2 = 'common'::text) AND (name2 = 'PPP'::text))
              Heap Fetches: 21
Planning time: 0.164 ms
Execution time: 0.131 ms

select count(distinct d.VALUE) as col_0_0_
from DATA d
where d.NAME1=?and d.NAME2=?and d.NAME3=?;

Aggregate  (cost=114425.94..114425.95 rows=1 width=8) (actual time=9457.205..9457.205 rows=1 loops=1)
  ->  Bitmap Heap Scan on data d  (cost=36196.62..113171.03 rows=501966 width=9) (actual time=256.187..1691.640 rows=502652 loops=1)
        Recheck Cond: (((name1)::text = 'common'::text) AND ((name2)::text = 'common'::text) AND ((name3)::text = 'PPP'::text))
        Rows Removed by Index Recheck: 2448858
        Heap Blocks: exact=41600 lossy=26550
          ->  Bitmap Index Scan on idx_constraint_dcdfk_tag_nm  (cost=0.00..36071.13 rows=501966 width=0) (actual time=243.261..243.261 rows=502668 loops=1)
                Index Cond: (((application_name)::text = 'common'::text) AND ((profile_name)::text = 'common'::text) AND ((tag_name)::text = 'PAN'::text))
Planning time: 0.174 ms
Execution time: 9457.931 ms

The actual result is 8542 milliseconds. I can't find the way how to reduce the time.

Answer 1

Your first query is fast because of the LIMIT — it uses the index to retrieve the rows in the ORDER BY order and stops after finding the first 5 results.

Your second query cannot be really fast, because it has to count a lot of rows. Note, however the lossy blocks during the Bitmap Heap Scan: You have those because your work_mem is too small to contain a bitmap with one bit per row.

If you increase work_mem, e.g. by

SET work_mem = '1GB';

the query will become substantially faster. Try until you find a value that is not too high, but avoids the lossy bitmap.