Re: index question

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Sun, May 1, 2016 at 5:40 PM, drum.lucas@xxxxxxxxx <drum.lucas@xxxxxxxxx> wrote:

Hi all,

I've got the following index on the gorfs.inode_segments table:

CREATE INDEX ix_clientids
  ON gorfs.inode_segments
  USING btree
  (("split_part"("full_path"::"text", '/'::"text", 4)::integer))
  WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");

And I'm running 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,
       i.st_ino,
       full_path
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 = 12225

- But the query does not use the index... Why?

Explain analyze:

"Seq Scan on "inode_segments"  (cost=0.00..3047212.44 rows=524846 width=63) (actual time=14212.466..51428.439 rows=31 loops=1)"
"  Filter: ("split_part"(("full_path")::"text", '/'::"text", 4) = '12225'::"text")"
"  Rows Removed by Filter: 104361402"
"Total runtime: 51428.482 ms"

Cheers

Lucas 

Well, a little more information would be useful like:

1. What is the PostgreSQL version?

2. What is the O/S?

3. What is the structure of gorfs.inode_segments?

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

--

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