Re: index question

On Sun, May 1, 2016 at 5:58 PM, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)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.