469,089 Members | 1,250 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,089 developers. It's quick & easy.

simple query runs very slowly

Dear group,

I got excellent help here a while back in optimizing a slow query, so I
thought I'd try again with another one...

I have a small table, around 3000 rows with two columns. One is a
timestamp and the other is an integer. Each day, a number of entries
are created, with integers ranging from 1 to 5.

I need to find how many entries with the integer of 3 are created per
day, so I do:

select date(tested) as testdate, count(*) as testcnt
from tests
where result = 3
group by date(tested)
order by date(tested);

This query runs fast, as expected. However, I also need the query to
return rows for the days where there aren't any integers of value 3
(but still integers of the other values.) So I do the following:

select date(t1.tested), t2.testcnt
from tests t1
left join (
select date(tested) as testdate, count(*) as testcnt
from tests
where result = 22
group by date(tested)
) t2
on t2.testdate = date(t1.tested)
group by date(t1.tested), t2.testcnt
order by date(t1.tested);

That is, I create a query that returns one date for each day and a
subquery that returns one date and count for each day that has integers
of value 3, and I join them on the dates.

Even though both the inner and outer queries run in a few microseconds
by themselves, when I join them up like this, the query takes three
seconds to run on a P4 2.5GHz.

The tests table has indexes on everything. I have vacuumed and
analyzed. This is the output from explain analyze:

QUERY PLAN

Group (cost=378748.88..378770.02 rows=282 width=20) (actual
time=3229.93..3234.55 rows=30 loops=1)
-> Sort (cost=378748.88..378755.93 rows=2819 width=20) (actual
time=3229.74..3231.94 rows=2819 loops=1)
Sort Key: date(t1.tested), t2.testcnt
-> Nested Loop (cost=263.38..378587.34 rows=2819 width=20)
(actual time=12.99..3207.15 rows=2819 loops=1)
Join Filter: ("inner".testdate = date("outer".tested))
-> Seq Scan on tests t1 (cost=0.00..245.19 rows=2819
width=8) (actual time=3.88..8.62 rows=2819 loops=1)
-> Subquery Scan t2 (cost=263.38..265.44 rows=27
width=8) (actual time=0.01..1.11 rows=16 loops=2819)
-> Aggregate (cost=263.38..265.44 rows=27
width=8) (actual time=0.01..1.08 rows=16 loops=2819)
-> Group (cost=263.38..264.75 rows=275
width=8) (actual time=0.01..0.79 rows=275 loops=2819)
-> Sort (cost=263.38..264.07
rows=275 width=8) (actual time=0.00..0.23 rows=275 loops=2819\
)
Sort Key: date(tested)
-> Seq Scan on tests
(cost=0.00..252.24 rows=275 width=8) (actual time=4.07..7.42 ro\
ws=275 loops=1)
Filter: (result =
'3'::text)
Total runtime: 3235.62 msec
(14 rows)

Unfortunately, I'm not very good at reading these, so I'm not sure
what's going on. It looks to me like maybe it's running the inner query
many times instead of just one? That would not be right because there
are no dependencies between the inner and the outer query. It should
only need to run each once. I'm running PostgreSQL 7.3.2.
Any help would be much appreciated.

Roger Dahl

Nov 23 '05 #1
0 2171

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Muharram Mansoorizadeh | last post: by
3 posts views Thread by Mike Ridley | last post: by
29 posts views Thread by wizofaus | last post: by
9 posts views Thread by Bob Darlington | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.