Re: Slow query - Mailing list pgsql-performance

EXPLAIN ANALYZE plan is shown below.
I also attached it as a file.

One thing that might help is that the query produces 27 rows, which is
much less than predicted 1963.

QUERY PLAN
 Sort (cost=553657.66..553662.57 rows=1963 width=218) (actual
time=133036.73..133036.75 rows=27 loops=1)
  Sort Key: medianame, status
  InitPlan
  -> Seq Scan on mediatype (cost=0.00..1.29 rows=1 width=8) (actual
time=0.12..0.14 rows=1 loops=1)
  Filter: (medianame = 'Audio'::character varying)
  -> Index Scan using media_mtype_index on media m (cost=0.00..553550.28
rows=1963 width=218) (actual  time=5153.36..133036.00 rows=27 loops=1)
  Index Cond: (mediatype = $0)
  Filter: (subplan)
  SubPlan
  -> Limit (cost=138.92..138.93 rows=1 width=24) (actual time=2.92..2.92
rows=0 loops=44876)
  -> Subquery Scan a1 (cost=138.92..138.93 rows=1 width=24) (actual
time=2.92..2.92 rows=0 loops=44876)
  -> Unique (cost=138.92..138.93 rows=1 width=24) (actual
time=2.91..2.91 rows=0 loops=44876)
  -> Sort (cost=138.92..138.93 rows=2 width=24) (actual time=2.91..2.91
rows=0 loops=44876)
  Sort Key: mediaid
  -> Append (cost=0.00..138.91 rows=2 width=24) (actual time=2.80..2.81
rows=0 loops=44876)
  -> Subquery Scan "*SELECT* 1" (cost=0.00..5.11 rows=1 width=8) (actual
time=0.06..0.06 rows=0 loops=44876)
  -> Index Scan using media_pkey on media (cost=0.00..5.11 rows=1
width=8) (actual time=0.05..0.05 rows=0  loops=44876)
  Index Cond: (objectid = $1)
  Filter: (activity = 347667::bigint)
  -> Subquery Scan "*SELECT* 2" (cost=24.25..133.80 rows=1 width=24)
(actual time=2.73..2.73 rows=0  loops=44876)
  -> Hash Join (cost=24.25..133.80 rows=1 width=24) (actual
time=2.72..2.72 rows=0 loops=44876)
  Hash Cond: ("outer"."set" = "inner".objectid)
  -> Index Scan using intsetmedia_media_index on intsetmedia ism
(cost=0.00..109.26 rows=38 width=16)  (actual time=0.04..0.04 rows=1
loops=44876)
  Index Cond: (media = $1)
  -> Hash (cost=24.24..24.24 rows=6 width=8) (actual time=0.14..0.14
rows=0 loops=44876)
  -> Index Scan using set_act_index on "set" s (cost=0.00..24.24 rows=6
width=8) (actual time=0.11..0.13  rows=2 loops=44876)
  Index Cond: (activity = 347667::bigint)
 Total runtime: 133037.49 msec


-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Monday, March 24, 2003 12:04 PM
To: Oleg Lebedev
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow query



On Mon, 24 Mar 2003, Oleg Lebedev wrote:

> Please help me speed up the following query. It used to run in 2-5
> sec., but now it takes 2-3 mins!

EXPLAIN ANALYZE output would be useful to see where the time is actually
taking place (rather than an estimate thereof).



*************************************

This email may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Any review, copying, printing, disclosure or other use is prohibited.
We reserve the right to monitor email sent through our network.

*************************************