As shaun has indicated, there is no need for join, also as Joshua suggested, it is good to upgrade your server. also add indexes for your predicates and foreign keys and you will get a desired result.
Regards
From: Shaun Thomas <sthomas@xxxxxxxxxxxxxxxx>
To: "Woolcock, Sean" <Sean.Woolcock@xxxxxxx>
Cc: "pgsql-performance@xxxxxxxxxxxxxx" <pgsql-performance@xxxxxxxxxxxxxx>
Sent: Monday, October 29, 2012 8:36 PM
Subject: Re: Request for help with slow query
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
Regards
From: Shaun Thomas <sthomas@xxxxxxxxxxxxxxxx>
To: "Woolcock, Sean" <Sean.Woolcock@xxxxxxx>
Cc: "pgsql-performance@xxxxxxxxxxxxxx" <pgsql-performance@xxxxxxxxxxxxxx>
Sent: Monday, October 29, 2012 8:36 PM
Subject: Re: Request for help with slow query
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
