Search Postgresql Archives

Re: Slow Query - PostgreSQL 9.2

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

 



On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> wrote:
> Hi Vitaly,
>
> Yep... gorfs.nodes is a view.
> And the schema is: gorfs.inode_segments
> So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
> Is that correct? It would be "st_ctime"?
If "inodes" is an alias for "gorfs.inode_segments" in your view, yes,
the above DDL is OK. According to EXPLAIN's "Filter" row the column
involving in comparison is st_ctime.

Hint: you can create the index without blocking table using "CREATE
INDEX CONCURRENTLY":
http://www.postgresql.org/docs/9.2/static/sql-createindex.html

> I've rewriten the query as well. Thank you for that!
>
> Thank you
> Lucas

>> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> Subject: Re:  Slow Query - PostgreSQL 9.2
>> From: vitaly.burovoy@xxxxxxxxx
>> To: smerlo50@xxxxxxxxxxx
>> CC: pgsql-general@xxxxxxxxxxxxxx
>>
>> On 1/10/16, Vitaly Burovoy <vitaly.burovoy@xxxxxxxxx> wrote:
>> > On 1/10/16, Saulo Merlo <smerlo50@xxxxxxxxxxx> wrote:
>> >> I've got a slow query.. I'd like to make it faster.. Make add an
>> >> index?
>> >> Query:
>> >> SELECT
>> >>   <<overquoting>>
>> >> FROM gorfs.nodes AS f
>> >>   <<overquoting>>
>> >> WHERE f.file_data IS NOT NULL
>> >>   AND ((transaction_timestamp() AT TIME ZONE 'UTC') > (f.last_changed
>> >> +
>> >> '24
>> >> months' :: INTERVAL)) LIMIT 100;
>> >
>> >> <<overquoting>>
>> >> "Total runtime: 94989.208 ms"What could I do to make it faster? Thank
>> >> you.
>> >
>> > At least you can add an index:
>> > CREATE INDEX ON gorfs.nodes(last_changed)
>> >
>> > and rewrite part of WHERE clause to:
>> > (f.last_changed < (transaction_timestamp() AT TIME ZONE 'UTC' - '24
>> > months'::INTERVAL))
>> >
>> > It allows to decrease the slowest part of your query (sequence
>> > scanning of a table, all 13.5M rows):
>> >> -> Seq Scan on "inodes" "t"  (cost=0.00..1411147.24 rows=13416537
>> >> width=29) (actual time=94987.224..94987.224 rows=0 loops=1)
>> >>      Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
>> >> (("st_ctime")::timestamp without time zone + '2 years'::interval))
>> >
>> > compare that time to the one in the topmost row of EXPLAIN:
>> >> Limit  (cost=1556.99..1336437.30 rows=100 width=186) (actual
>> >> time=94987.261..94987.261 rows=0 loops=1)
>>
>> Hmm. It seems that gorfs.nodes is a view.
>> So creating index should be something like (I have no idea that schema
>> name for it):
>> CREATE INDEX ON _schema_name_.inodes(st_ctime)

-- 
Best regards,
Vitaly Burovoy


-- 
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