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@xxxxxxxxxxxxxxxx
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
