Re: Request for help with slow query

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

 



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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux