I am trying to understand why my query takes a really long time even though I have the required columns indexed:
SELECT entity_id,
id,
report_date
FROM own_inst_detail
WHERE ( own_inst_detail.id = 'P7M7WC-S' )
AND ( own_inst_detail.report_date >= '2017-02-01T17:29:49.661Z' )
AND ( own_inst_detail.report_date <= '2018-08-01T17:29:49.663Z' )
The cached result of EXPLAIN ANALYZE
is as follows:
Bitmap Heap Scan on own_inst_detail (cost=20.18..2353.55 rows=597 width=22) (actual time=1.471..6.955 rows=4227 loops=1)
Recheck Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
Heap Blocks: exact=4182
-> Bitmap Index Scan on own_inst_detail (cost=0.00..20.03 rows=597 width=0) (actual time=0.901..0.901 rows=4227 loops=1)
Index Cond: ((id = 'P7M7WC-S'::bpchar) AND (report_date >= '2017-06-01'::date) AND (report_date <= '2018-08-01'::date))
Planning time: 0.123 ms
Execution time: 7.801 ms
This part of the query takes 4 out of 5 total seconds that my full query takes.
I have combined index for id and report_date. I also have two standalone indexes for those columns.
I've tried playing around with setting high work_mem as well as lowering the random_page_cost but nothing really helps.
Any additional suggestions are greatly appreciated.
I found similar question How to index WHERE (start_date >= '2013-12-15') which recommends adding a B-Tree index, but I already do have index for report_date.
Create table script:
CREATE TABLE IF NOT EXISTS public.own_inst_detail (
entity_id character(8) NOT NULL,
id character(8) NOT NULL,
report_date date NOT NULL,
PRIMARY KEY(report_date)
);
Index:
CREATE INDEX indx_own_inst_detail_report_date_desc ON own_inst_detail (report_date DESC NULLS LAST)
CREATE INDEX indx_own_inst_detail_id_report_date_desc ON own_inst_detail (id, report_date DESC NULLS LAST)
CREATE TABLE
statement) and relevant index definitions for performance optimization. And all other relevant details. Consider instructions in the postgresql-performance tag info.date
in the query plan but you usetimestamp
literals in the query. Andbpchar
seems like a misunderstanding. Neither may be the root of the perf problem, but both may cause problems.EXPLAIN (ANALYZE, BUFFERS)
. Better yet, turn on track_io_timing first as well.SELECT version()
btw.