Search Postgresql Archives

Re: (VERY) Slow Query - PostgreSQL 9.2

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

 



> On 03 May 2016, at 11:55, drum.lucas@xxxxxxxxx wrote:
> 
> Hi all,
> 
> I'm trying to get the query below a better performance.. but just don't know what else I can do...
> 
> Please, have a look and let me know if you can help somehow.. also.. if you need some extra data jet ask me please.
> 
> * Note that the gorfs.inode_segments table is 1.7TB size
> 
> I have the following Query:
> 
> explain analyze
> 
> 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,
>        (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;

(Stripped the 1-and-a-half extra queries in there, but that incomplete one might be why you're waiting?)

> 	• Explain analyze link: http://explain.depesz.com/s/Oc6
> The query is taking ages, and I can't get the problem solved.
> 
> These are the index I've already created on the inode_segments table:


> What else can I do to improve the Performance of the Query?

The first thing I notice in your query is that you're making use of hierarchically organised data without storing it hierarchically, namely that full_path field. The result of that is that both your table and your index contain a lot of redundant information.

Now I'm not so sure a hierarchical table + query are going to help get you much performance out of this (probably worth an experiment or two, mind that O/S's usually use inode trees for such things), but reducing the redundancy in the index would probably help:

create index gorfs.inodes_accounts_idx on gorfs.inodes (substring (full_path from 20)) where full_path like '/userfiles/account/%';

and then use similar expressions in your query of course:

where full_path like '/userfiles/account/%' and substring(full_path from 20) ~ '^[0-9]+/[a-z]+/[0-9]+';

Good luck!
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[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