7.1 slowed down my query - Mailing list pgsql-general

From dustin sallings
Subject 7.1 slowed down my query
Date
Msg-id Pine.NEB.4.33.0104151210570.3002-100000@foo.west.spy.net
Whole thread Raw
List pgsql-general
    Hey, can someone give me an idea here?  I've got the following
query:

select
    m.music_id, m.title
    from
        music m, music_sub_crossref x, music_subscribers s
    where
        m.cat=x.cat_id
        and s.subscriber_id=x.subscriber_id
        and s.subscriber_id=778
        and m.added >= s.sub_start
        and not exists
            (select 1
                from music_download_log d
                where
                    d.music_id=m.music_id
                    and d.pager_id = s.pager_id
            )

Now, explain tells me decent stuff:

Nested Loop  (cost=0.00..32501.18 rows=1 width=56)
  ->  Nested Loop  (cost=0.00..4.04 rows=2 width=28)
        ->  Index Scan using music_subscri_subscriber_id_key on music_subscribers s  (cost=0.00..2.01 rows=1 width=20)
        ->  Index Scan using music_sub_crossref_bys on music_sub_crossref x  (cost=0.00..2.01 rows=1 width=8)
  ->  Index Scan using music_bycat on music m  (cost=0.00..18.34 rows=24 width=28)
  SubPlan
    ->  Index Scan using music_download_log_bymusic on music_download_log d  (cost=0.00..645.35 rows=1 width=0)

Looks like it's going to be using each index I intended it to, however,
the query isn't very quick (anymore).  I just timed 19.51s on that
particular query against my 7.1.  I don't have timings on my 7.0, but I
can give a bit of insight...

    I run a query to get a list of subscriber IDs, then I run this
query on each subscriber ID.  There are about 300 subscribers and my
entire application runtime has gone from 1274.652s (the morning of Friday
13th) to 5944.964s yesterday and 5619.084s today.

    Any clues?

--
dustin sallings                            The world is watching America,
http://1081689538/~dustin/                 and America is watching TV.


pgsql-general by date:

Previous
From: Nelson Ferreira Jr
Date:
Subject: Re: How to store gif or jpeg? Thanks!
Next
From: Ned Lilly
Date:
Subject: Re: Benchmarking PostgreSQL