432,463 Members | 771 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,463 IT Pros & Developers. It's quick & easy.

# simple query runs very slowly

 P: n/a 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 