Re: Request for help with slow query - Mailing list pgsql-performance

From Albe Laurenz
Subject Re: Request for help with slow query
Date
Msg-id D960CB61B694CF459DCFB4B0128514C2089A610A@exadv11.host.magwien.gv.at
Whole thread Raw
In response to Request for help with slow query  ("Woolcock, Sean" <Sean.Woolcock@emc.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: "Albe Laurenz"
Date:
Subject: Re: Replaying 48 WAL files takes 80 minutes
Next
From: Mahavir Trivedi
Date:
Subject: out of memory