Re: index question - Mailing list pgsql-general

From Melvin Davidson
Subject Re: index question
Date
Msg-id CANu8FizWCtD_L=s0id1-vHE7B=1tQmd3dwvt7xzx7FE9TcXvdg@mail.gmail.com
Whole thread Raw
In response to Re: index question  ("drum.lucas@gmail.com" <drum.lucas@gmail.com>)
Responses Re: index question
List pgsql-general


On Sun, May 1, 2016 at 5:58 PM, drum.lucas@gmail.com <drum.lucas@gmail.com> wrote:


Well, a little more information would be useful like:

Ops.. yes sure.. sorry about that.
 
1. What is the PostgreSQL version?

PostgreSQL 9.2
 
2. What is the O/S?

Linux Centos 6.7 64 bits
 
3. What is the structure of gorfs.inode_segments?

Table inode_segments: (I'll leave the comments to help)
CREATE TABLE gorfs.inode_segments
(
  st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope
  segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...
  st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)
  full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)
  segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
  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 gorfs.inodes:
CREATE TABLE gorfs.inodes
(
  st_dev "gorfs"."dev_t" DEFAULT NULL::bigint, -- ID of device containing file. Meaningless in this implementation....
  st_ino "gorfs"."ino_t" NOT NULL DEFAULT "nextval"('"gorfs"."inodes_st_ino_idseq"'::"regclass"), -- Inode number....
  st_mode "gorfs"."mode_t" NOT NULL, -- File type/mode bits....
  st_nlink "gorfs"."nlink_t" NOT NULL, -- Number of hard links (directory segments) pointing to this inode. See stat(2) manual page for details (man 2 stat)
  st_uid "gorfs"."uid_t" NOT NULL, -- User ID that owns the file. See stat(2) manual page for details (man 2 stat)
  st_gid "gorfs"."gid_t" NOT NULL, -- Group ID that owns the file.See stat(2) manual page for details (man 2 stat)
  st_rdev "gorfs"."dev_t", -- Device number (currently we don't support device files).  See stat(2) manual page for details (man 2 stat)
  st_size "gorfs"."off_t", -- File size, if applicable. See stat(2) manual page for details (man 2 stat)
  st_blksize "gorfs"."blksize_t", -- Block size for I/O. Meaningless here, hard coded to 512. See stat(2) manual page for details (man 2 stat)
  st_blocks "gorfs"."blkcnt_t", -- Number of allocated blocks. Meaningless here, but calculated from block size. See stat(2) manual page for details (man 2 stat)
  st_atime "gorfs"."time_t" NOT NULL, -- Timestamp of last access. Stored as a timestamp as opposed to unix TS. See stat(2) manual page for details (man 2 stat)
  st_mtime "gorfs"."time_t" NOT NULL, -- Timestamp of last modification. Stored as a timestamp as opposed to unix TS. See stat(2) manual page for details (man 2 stat)
  st_ctime "gorfs"."time_t" NOT NULL, -- Timestamp of last change. Stored as a timestamp as opposed to unix TS. See stat(2) manual page for details (man 2 stat)
  checksum_md5 "md5_hash", -- MD5 checksum of the file. Supplied by the application as the DB might not even see the payload
  media_subtype_id integer, -- Reference to MIME type (see FK constraint). We can't support all media types but unknow types can be stored as application/octet-stream
  external_size "gorfs"."off_t", -- For symlinks only. Meaningful for fat links only: total size of the fat link target. Null for normal symlinks
  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)
)


4. Did you do an ANALYZE table gorfs.inode_segments   after you created the index?

Yes.. actually the index was already created. 

Well, it looks like David's explanation is correct.
Your index is based on split_part function
but the WHERE clause is specific to full_path, so the planner cannot find a valid index

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

pgsql-general by date:

Previous
From: "drum.lucas@gmail.com"
Date:
Subject: Re: index question
Next
From: "David G. Johnston"
Date:
Subject: Re: index question