[PERFORM] Dissuade the use of exclusion constraint index
Hey all,
I'm using Postgres 10.3
6 core VM with 16gb of ram
My database schema requires a good bit of temporal data stored in a
few my tables, and I make use of ranges and exclusion constraints to
keep my data consistent.
I have quite a few queries in my DB which are using a very sub-optimal
index choice compared to others available. I am just looking for ways
to tune things to make it less likely to use the backing index for an
exclusion constraint for queries where better indexes are available.
Here is an example of a query which exhibits this behavior:
SELECT *
FROM claim
INNER JOIN claim_amounts
ON claim.claim_id = claim_amounts.claim_id
LEFT JOIN deduction_claim
ON deduction_claim.claim_id = claim.claim_id
AND upper_inf(deduction_claim.active_range)
WHERE claim.claim_id = ANY ('{uuids_go_here}'::uuid[]);
Here is the plan which is always chosen: https://explain.depesz.com/s/rCjO
I then dropped the exclusion constraint temporarily to test, and this
was the plan chosen after: https://explain.depesz.com/s/xSm0
The table definition is:
CREATE TABLE deduction_claim
(
deduction_id uuid NOT NULL,
claim_id uuid NOT NULL,
deduction_amount_allotted numeric NOT NULL,
active_range tstzrange NOT NULL DEFAULT tstzrange(now(),
NULL::timestamp with time zone),
inoperative boolean DEFAULT false,
deduction_claim_id uuid NOT NULL DEFAULT gen_random_uuid(),
CONSTRAINT deduction_claim_pkey PRIMARY KEY (deduction_claim_id),
CONSTRAINT deduction_claim_claim_id_fkey FOREIGN KEY (claim_id)
REFERENCES claim (claim_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT deduction_claim_deduction_id_fkey FOREIGN KEY (deduction_id)
REFERENCES deduction (deduction_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT deduction_claim_active_range_excl EXCLUDE
USING gist (deduction_id WITH =, claim_id WITH =, active_range WITH &&),
CONSTRAINT deduction_claim_ar_empty_check CHECK (active_range <>
'empty'::tstzrange)
);
-- Index: idx_deduction_claim_claim_id
-- DROP INDEX idx_deduction_claim_claim_id;
CREATE INDEX idx_deduction_claim_claim_id
ON deduction_claim
USING btree
(claim_id)
WHERE upper_inf(active_range);
-- Index: idx_deduction_claim_deduction_id
-- DROP INDEX idx_deduction_claim_deduction_id;
CREATE INDEX idx_deduction_claim_deduction_id
ON deduction_claim
USING btree
(deduction_id)
WHERE upper_inf(active_range);
If there is any more info I can provide, please let me know.
Thanks in advance for any advice you can give.
citext performance
Folks, I read following (PostgreSQL: Documentation: 9.6: citext) and it does
not hold true in my testing.. i.e citext is not performing better than lower.Am
I missing something? help is appreciated.
|
|
|
| | |
|
|
|
| |
PostgreSQL: Documentation: 9.6: citext
|
|
|
"citext is not as efficient as text because the operator functions and the
B-tree comparison functions must make copies of the data and convert it to
lower case for comparisons. It is, however, slightly more efficient than using
lower to get case-insensitive matching."
Here is what I have done
drop table test;drop table testci;
CREATE TABLE test (id INTEGER PRIMARY KEY,name character varying(254));CREATE
TABLE testci (id INTEGER PRIMARY KEY,name citext
);
INSERT INTO test(id, name)SELECT generate_series(101,200),
(md5(random()::text));
INSERT INTO testci(id, name)SELECT generate_series(1,100),
(md5(random()::text));
Now, I have done sequential search
explain (analyze on, format yaml) select * from test where
lower(name)=lower('f6d7d5be1d0bed1cca11540d3a2667de');
- Plan: Node Type: "Seq Scan" Parallel Aware: false Relation Name:
"test" Alias: "test" Startup Cost: 0.00 Total Cost: 23334.00 Plan
Rows: 5000 Plan Width: 37 Actual Startup Time: 0.016 Actual Total
Time: 680.199 Actual Rows: 1 Actual Loops: 1 Filter:
"(lower((name)::text) = 'f6d7d5be1d0bed1cca11540d3a2667de'::text)" Rows
Removed by Filter: 99 Planning Time: 0.045 Triggers: Execution Time:
680.213
explain (analyze on, format yaml) select * from testci where
name='956d692092f0b9f85f36bf2b2501f3ad';
- Plan: Node Type: "Seq Scan" Parallel Aware: false Relation Name:
"testci" Alias: "testci" Startup Cost: 0.00 Total Cost: 20834.00
Plan Rows: 1 Plan Width: 37 Actual Startup Time: 0.017 Actual Total
Time: 1184.485 Actual Rows: 1 Actual Loops: 1 Filter: "(name =
'956d692092f0b9f85f36bf2b2501f3ad'::citext)" Rows Removed by Filter: 99
Planning Time: 0.029 Triggers: Execution Time: 1184.496
You can see sequential searches with lower working twice as fast as citext.
Now I added index on citext and equivalent functional index (lower) on text.
CREATE INDEX textlowerindex ON test (lower(name));
create index textindex on test(name);
Index creation took longer with citext v/s creating lower functional index.
Now here comes execution with indexes
explain (analyze on, format yaml) select * from test where
lower(name)=lower('f6d7d5be1d0bed1cca11540d3a2667de');
- Plan: Node Type: "Bitmap Heap Scan" Parallel Aware: false
Relation Name: "test" Alias: "test" Startup Cost: 187.18 Total Cost:
7809.06 Plan Rows: 5000 Plan Width: 37 Actual Startup Time: 0.020
Actual Total Time: 0.020 Actual Rows: 1 Actual Loops: 1 Recheck Cond:
"(lower((name)::text) = 'f6d7d5be1d0bed1cca11540d3a2667de'::text)" Rows
Removed by Index Recheck: 0 Exact Heap Blocks: 1 Lossy Heap Blocks: 0
Plans: - Node Type: "Bitmap Index Scan" Parent Relationship:
"Outer" Parallel Aware: false Index Name: "textlowerindex"
Startup Cost: 0.00 Total Cost: 185.93 Plan Rows: 5000 Plan
Width: 0 Actual Startup Time: 0.016 Actual Total Time: 0.016
Actual Rows: 1 Actual Loops: 1 Index Cond:
"(lower((name)::text) = 'f6d7d5be1d0bed1cca11540d3a2667de'::text)" Planning
Time: 0.051 Triggers: Execution Time: 0.035
explain (analyze on, format yaml) select * from test where
lower(name)=lower('f6d7d5be1d0bed1cca11540d3a2667de');
- Plan: Node Type: "Index Scan" Parallel Aware: false Scan
Direction: "Forward" Index Name: "citextindex" Relation Name: "testci"
Alias: "testci" Startup Cost: 0.42 Total Cost: 8.44 Plan Rows: 1
Plan Width: 37 Actual Startup Time: 0.049 Actual Total Time: 0.050
Actual Rows: 1 Actual Loops: 1 Index Cond: "(name =
'956d692092f0b9f85f36bf2b2501f3ad'::citext)" Rows Removed by Index Recheck:
0 Planning Time: 0.051 Triggers: Execution Time: 0.064
Deepak
