index creation - Slow Query - PostgreSQL 9.2

Question

I've got the following query:

SELECT
* FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0) as test WHERE account_id = 12115;

Explain analyze for the query above: http://explain.depesz.com/s/rHOU

It seems the query is not using the Index I've created: ix_full_path

Table inodes_segments:

CREATE TABLE gorfs.inode_segments
(
  st_ino "gorfs"."ino_t" NOT NULL,
  segment_index "gorfs"."pathname_component" NOT NULL,
  st_ino_target "gorfs"."ino_t",
  full_path "gorfs"."absolute_pathname",
  segment_data "bytea",
  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
  CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)

Table inodes:

CREATE TABLE gorfs.inodes
(
  st_dev "gorfs"."dev_t" DEFAULT NULL::bigint,
  st_ino "gorfs"."ino_t" NOT NULL DEFAULT "nextval"('"gorfs"."inodes_st_ino_idseq"'::"regclass"),
  st_mode "gorfs"."mode_t" NOT NULL,
  st_nlink "gorfs"."nlink_t" NOT NULL,
  st_uid "gorfs"."uid_t" NOT NULL,
  st_gid "gorfs"."gid_t" NOT NULL,
  st_rdev "gorfs"."dev_t",
  st_size "gorfs"."off_t",
  st_blksize "gorfs"."blksize_t",
  st_blocks "gorfs"."blkcnt_t",
  st_atime "gorfs"."time_t" NOT NULL,
  st_mtime "gorfs"."time_t" NOT NULL,
  st_ctime "gorfs"."time_t" NOT NULL,
  checksum_md5 "md5_hash",
  media_subtype_id integer,
  external_size "gorfs"."off_t",
  CONSTRAINT pk_inodes PRIMARY KEY ("st_ino"),
  CONSTRAINT fk_media_subtype_must_exist FOREIGN KEY (media_subtype_id)
      REFERENCES public.media_subtypes (media_subtype_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cc_mount_devices_not_supported CHECK ("st_dev" IS NULL)
)

I've already created the following indexes:

CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (("full_path")::"text");
CREATE INDEX CONCURRENTLY ix_checksum_st_size ON gorfs.inodes USING btree ("checksum_md5","st_size") WHERE checksum_md5 IS NOT NULL;

• But it didn't work.

Question:

What can I do to improve the Query?

Answer 1

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.:

AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+'

Alternatively, create the index without typecasting:

CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path);

Note also that the character / in regexp has a special meaning, and although ~ can deal with it unescaped (because it is implied), other regexp functions (such as regexp_replace or regexp_matches) will likely fail, so you may want to always escape them, as a general practice:

AND (s.full_path)::text ~ '\/userfiles\/account\/[0-9]+\/[a-z]+\/[0-9]+'

In any case, regex expressions may not benefit from a present BTREE index, unless they're anchored:

AND (s.full_path)::text ~ '^\/userfiles\/account\/[0-9]+\/[a-z]+\/[0-9]+'

The query may still not use the index if the regex comparison is anchored, when it considers that the results would not actually filter much (from collected statistics) and a sequential scan would perform better, e.g. when most of the rows in that table actually start with /userfiles/account/.

Edit - optimising the query

Your query is not optimised for large sets. Firstly, most of you filtering is based on a non-indexed comparison (split_part(full_path, '/', 4)::INT = 12115). Secondly, you apply the filtering condition to the results of the join; PostgreSQL is often smart enough to translate the filter to the subquery, but it is best to construct your queries in a way that will ensure that PostgreSQL will the do the right thing.

For example, filter by account_id directly from full_path:

CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); -- ensure your index is not typecasted

SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/12115/[a-z]+/[0-9]+' -- filter account directly into string comparison
  AND i.st_size > 0;

Alternatively, you can create a function index by the account_id part of full_path:

-- This index is both useful to filter any rows that do not conform to the below full_path formatting
-- and to search within them for a numeric account_id
-- (note: the index would only be used if the exact same regex comparison is performed on full_path when searching for an account_id)
CREATE INDEX CONCURRENTLY ix_account_id_from_full_path ON gorfs.inode_segments USING btree ((split_part(full_path, '/', 4)::INT))
WHERE (full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+');

SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE split_part(s.full_path, '/', 4)::INT = 12115
  AND i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0;