pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with understanding EXPLAIN
2+ messages / 2 participants
[nested] [flat]
* help with understanding EXPLAIN
@ 2010-10-13 21:18 Brandon Casci <brandon@loudcaster.com>
0 siblings, 1 reply; 2+ messages in thread
From: Brandon Casci @ 2010-10-13 21:18 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)
--
=========================================
Brandon Casci
Loudcaster
http://loudcaster.com
=========================================
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [PERFORM] help with understanding EXPLAIN
@ 2010-10-16 01:17 Craig Ringer <craig@postnewspapers.com.au>
parent: Brandon Casci <brandon@loudcaster.com>
0 siblings, 0 replies; 2+ messages in thread
From: Craig Ringer @ 2010-10-16 01:17 UTC (permalink / raw)
To: Brandon Casci <brandon@loudcaster.com>; List, Postgres <pgsql-general@postgresql.org>
On 10/14/2010 05:18 AM, Brandon Casci wrote:
> 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?
That query plan is nearly illegible, and it contains no information
about how the query actually executed or how many rows were fetched from
various tables. I can't personally see any way to give you useful advice
based on that information.
I know you're going through a third party, but do think you could get
them to post the original form, unmangled by mail clients and whatever
else they've done to it, here? :
http://explain.depesz.com
Get them to use EXPLAIN ANALYZE rather than plain EXPLAIN, and to
provide the text of the query that generated the plan.
--
Craig Ringer
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-16 01:17 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 21:18 help with understanding EXPLAIN Brandon Casci <brandon@loudcaster.com>
2010-10-16 01:17 ` Craig Ringer <craig@postnewspapers.com.au>
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