Re: Request for help with slow query

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