Message-ID: <508EDAB5.3090709@optionshouse.com>
Date: Mon, 29 Oct 2012 14:36:21 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
Reply-To: <sthomas@optionshouse.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121011 Thunderbird/16.0.1
MIME-Version: 1.0
To: "Woolcock, Sean" <Sean.Woolcock@emc.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Request for help with slow query
References: <998490E92A198A48BE60F3A9C44CA3F1010A7DCF3901@MX40A.corp.emc.com>
In-Reply-To: <998490E92A198A48BE60F3A9C44CA3F1010A7DCF3901@MX40A.corp.emc.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

On 10/29/2012 12:41 PM, Woolcock, Sean wrote:

>      An example query that's running slowly for me is:
>
>          select tape.volser,
>                 tape.path,
>                 tape.scratched,
>                 tape.size,
>                 extract(epoch from tape.last_write_date) as last_write_date,
>                 extract(epoch from tape.last_access_date) as last_access_date
>              from tape
>              inner join filesystem
>                  on (tape.filesystem_id = filesystem.id)
>              order by last_write_date desc
>              limit 100
>              offset 100;

Is this a representative example? From the looks of this, you could 
entirely drop the join against the filesystems table, because you're not 
using it in the SELECT or WHERE sections at all. You don't need that 
join in this example.

> ->  Seq Scan on tape  (cost=0.00..178550.57 rows=3219757 width=312)
> (actual time=2.824..18175.863 rows=3219757 loops=1)
> ->  Hash  (cost=3.01..3.01 rows=101 width=4) (actual
> time=0.204..0.204 rows=101 loops=1)
> ->  Seq Scan on filesystem  (cost=0.00..3.01 rows=101 width=4)
> (actual time=0.004..0.116 rows=101 loops=1)
>          Total runtime: 66553.643 ms

I think we can stop looking at this point. Because of the ORDER clause, 
it has to read the entire tape table because you have no information on 
last_write_date it can use. Then, it has to read the entire filesystem 
table because you asked it to do a join, even if you threw away the results.

>      1. I added an index on last_write_date with:
>         and there was no improvement in query time.

I'm not sure 8.1 knows what to do with that. But I can guarantee newer 
versions would do a reverse index scan on this index to find the top 100 
rows, even with the offset. You can also do this with newer versions, 
since it's the most common query you run:

create index tape_last_write_date_idx on tape (last_write_date DESC);

Which would at least give you forward read order when addressing this index.

> 3. I ran the query against the same data in Postgres 9.1.6 rather than 8.1.17
>    using the same hardware and it was about 5 times faster (nice work,

It would be an order of magnitude faster than that if you add the index 
also.

> Unfortunately upgrading is not an option, so this is more of an
> anecdote. I would think the query could go much faster in either
> environment with some optimization.

You desperately need to reconsider this. PostgreSQL 8.1 is no longer 
supported, and was last updated in late 2010. Any bug fixes, including 
known corruption and security bugs, are no longer being backported. 
Every day you run on an 8.1 install is a risk. The story is similar with 
8.2. Even 8.3 is on the way to retirement. You're *six* major versions 
behind the main release.

At the very least, you need to upgrade PostgreSQL from 8.1.17 to 8.1.23. 
You're still on a version of PG that's almost 7-years old, but at least 
you'd have the most recent patch level.


-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email