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.
