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
