0

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..

4
  • Is myschemadb in search_path? If not, can you try adding it and see if that makes a difference? Jul 24 '20 at 16:39
  • Did you run 'vacuum analyze' after creating index? Also I recommend pg_trgrm for text searches Jul 29 '20 at 19:17
  • @DanielVérité dont make difference. But help me, because i found that schema was a problem.
    – Daniel1791
    Jul 30 '20 at 19:42
  • @YavuzSelim yes, i run vacuum analyze, i tried to change random_past_cost parameter, change table statistic, but the solution was alter extension to public schema.
    – Daniel1791
    Jul 30 '20 at 19:44
0

Thanks you all!

With a Daniel Vérité help, I found the problem.

The citext extension must be created without "schema". When i created i used:

CREATE EXTENSION citext WITH SCHEMA myschemadb 

But the index is used only when citext is on public schema.

So, you can do this:

CREATE EXTENSION citext WITH SCHEMA public

or this:

CREATE EXTENSION citext 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.