The following query is returning a SEQ Scan instead a Index.
select file_name from myschemadb.my_files where file_name = 'djsaidjasdjoasdjoaidad'
Engine
Postgres 11.5
My table:
CREATE TABLE myschemadb.my_files
id int4 NOT NULL,
file_name myschemadb.citext NOT NULL,
status_id int4 NOT NULL,<br />
file_key myschemadb.citext NOT NULL,
is_fine bool NOT NULL DEFAULT true,
create_date timestamptz NOT NULL DEFAULT now(),
update_date timestamptz NULL,
CONSTRAINT pk_my_files PRIMARY KEY (id)
);
The created index:
CREATE INDEX my_files_file_name_idx ON myschemadb.my_files USING btree (file_name);
Execution Plan
[
{
"Plan": {
"Node Type": "Gather",
"Parallel Aware": false,
"Startup Cost": 1000,
"Total Cost": 70105.63,
"Plan Rows": 1,
"Plan Width": 41,
"Actual Startup Time": 109.537,
"Actual Total Time": 110.638,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": [
"file_name"
],
"Workers Planned": 2,
"Workers Launched": 2,
"Single Copy": false,
"Shared Hit Blocks": 58326,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Relation Name": "my_files",
"Schema": "myschemadb",
"Alias": "my_files",
"Startup Cost": 0,
"Total Cost": 69105.53,
"Plan Rows": 1,
"Plan Width": 41,
"Actual Startup Time": 107.42,
"Actual Total Time": 107.42,
"Actual Rows": 0,
"Actual Loops": 3,
"Output": [
"file_name"
],
"Filter": "((my_files.file_name)::text = 'djsaidjasdjoasdjoaidad'::text)",
"Rows Removed by Filter": 690443,
"Shared Hit Blocks": 58326,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0,
"Workers": [
{
"Worker Number": 0,
"Actual Startup Time": 106.121,
"Actual Total Time": 106.121,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 15754,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0
},
{
"Worker Number": 1,
"Actual Startup Time": 106.821,
"Actual Total Time": 106.821,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 26303,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0,
"I/O Write Time": 0
}
]
}
]
},
"Planning Time": 0.034,
"Triggers": [],
"Execution Time": 110.652
}
]
I guess the problem is here:
"Filter": "((my_files.file_name)::text = 'djsaidjasdjoasdjoaidad'::text)",
This implicit conversion can be a problem. But when i make a explicit conversion doesnt work too:
select file_name from myschemadb.file_history where file_name = 'djsaidjasdjoasdjoaidad'::myschemadb.citext
I see this link: Why does a comparison between CITEXT and TEXT fail?
but didn't help me..
myschemadbinsearch_path? If not, can you try adding it and see if that makes a difference?