Re: Request for help with slow query

Sean Woolcock wrote:
> I have a large (3 million row) table called "tape" that represents
files,
> which I join to a small (100 row) table called "filesystem" that
represents
> filesystems. I have a web interface that allows you to sort by a
number of
> fields in the tape table and view the results 100 at a time (using
LIMIT
> and OFFSET).
>
> The data only changes hourly and I do a "vacuum analyze" after all
changes.

> 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;
>
> On Postgres 8.1.17 this takes about 60 seconds. I would like it to
be faster.

> Here's a depesz link with that output:
http://explain.depesz.com/s/AUR

I don't see anything obviously wrong there.

At least the sequential scan on "tape" is necessary.

> Things I've tried:
[...]
> 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,
> whoever did that!). 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.

Can you post EXPLAIN ANALYZE for the query on 9.1.6?

Staying on 8.1 is not a good idea, but I guess you know that.

> Storage details (important for performance and corruption questions):
> Do you use a RAID controller?
> No.
> How many hard disks are connected to the system and what types are
they?
> We use a single Hitachi HDT72102 SATA drive (250GB) 7200 RPM.
> How are your disks arranged for storage?
> Postgres lives on the same 100GB ext3 partition as the OS.

I'd say that a query like this will always be disk bound.
Getting faster storage should help.

Yours,
Laurenz Albe