pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with understanding EXPLAIN and boosting performance
2+ messages / 2 participants
[nested] [flat]
* help with understanding EXPLAIN and boosting performance
@ 2010-10-13 22:06 Brandon Casci <brandon@loudcaster.com>
2010-10-15 18:35 ` Re: help with understanding EXPLAIN and boosting performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Brandon Casci @ 2010-10-13 22:06 UTC (permalink / raw)
To: pgsql-performance
Hello
I have an application hosted on Heroku. They use postgres. It's more or less
abstracted away, but I can get some performance data from New Relic. For the
most part, performance is ok, but now and then some queries take a few
seconds, and spike up to 15 or even 16 seconds! Ouch!
This is the most detailed information I could get form New Relic. Do you
have any suggestions how I could improve the performance?
QUERY PLANLimit (cost=26.49..1893.46 rows=1 width=4) QUERY PLAN -> Unique
(cost=26.49..44833.82 rows=24 width=4) QUERY PLAN -> Nested Loop
(cost=26.49..44833.81 rows=24 width=4) QUERY PLAN -> Merge Join
(cost=26.49..44532.99 rows=4773 width=8) QUERY PLAN Merge Cond: (songs.id =
artists_songs.song_id) QUERY PLAN -> Index Scan using songs_pkey on songs
(cost=0.00..25219.30 rows=4773 width=4) QUERY PLAN Filter:
(lower((name)::text) = 'thirteen'::text) QUERY PLAN -> Index Scan using
index_artists_songs_on_song_id on artists_songs (cost=0.00..18822.04
rows=960465 width=8) QUERY PLAN -> Index Scan using artists_pkey on artists
(cost=0.00..0.06 rows=1 width=4) QUERY PLAN Index Cond: (artists.id =
artists_songs.artist_id) QUERY PLAN Filter: (lower((artists.name)::text) =
'red mountain church'::text)
Thanks!
--
=========================================
Brandon Casci
Loudcaster
http://loudcaster.com
=========================================
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: help with understanding EXPLAIN and boosting performance
2010-10-13 22:06 help with understanding EXPLAIN and boosting performance Brandon Casci <brandon@loudcaster.com>
@ 2010-10-15 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-10-15 18:35 UTC (permalink / raw)
To: Brandon Casci <brandon@loudcaster.com>; +Cc: pgsql-performance
Brandon Casci <brandon@loudcaster.com> writes:
> I have an application hosted on Heroku. They use postgres. It's more or less
> abstracted away, but I can get some performance data from New Relic. For the
> most part, performance is ok, but now and then some queries take a few
> seconds, and spike up to 15 or even 16 seconds! Ouch!
The particular query you're showing here doesn't look terribly
expensive. Are you sure this is one that took that long?
If you're seeing identical queries take significantly different times,
I'd wonder about what else is happening on the server. The most obvious
explanation for that type of behavior is that everything is swapped into
RAM when it's fast, but has to be read from disk when it's slow. If
that's what's happening you should consider buying more RAM or
offloading some activities to other machines, so that there's not so
much competition for memory space.
If specific queries are consistently slow then EXPLAIN might give some
useful info about those. It's unlikely to tell you much about
non-reproducible slowdowns, though.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-15 18:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-13 22:06 help with understanding EXPLAIN and boosting performance Brandon Casci <brandon@loudcaster.com>
2010-10-15 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox