Обсуждение: help with understanding EXPLAIN and boosting 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
=========================================

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