TransWikia.com

Postgres not using index in citext column

Database Administrators Asked by Daniel1791 on December 31, 2021

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

One Answer

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 

Answered by Daniel1791 on December 31, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP