Search Postgresql Archives

Re: index question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux