| From: | "Chad Thompson" <chad(at)weblinkservices(dot)com> | 
|---|---|
| To: | "pgsql-performance" <pgsql-performance(at)postgresql(dot)org> | 
| Subject: | Max time queries | 
| Date: | 2002-10-17 14:45:07 | 
| Message-ID: | 068c01c275eb$c9ba4f70$32021aac@chad | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-performance | 
Heres an oddity.  Why would it take more time to not find an answer than it would to find one?  
Here are my 2 queries.
The Cold Fusion output of the query is followed by an explain analyze.
maxTime (Records=0, Time=2223ms)
SQL = 
select cr.start_time as max
				from call_results cr, timezone tz, lists l
				where (cr.start_time between '10/15/2002 08:00' and '10/15/2002 23:00')
				and l.full_phone = cr.phonenum
				and l.area_code = tz.area_code
				and tz.greenwich = '-7'
				and cr.project_id = 11
				and l.client_id = 8 
				order by cr.start_time desc
				limit 1
NOTICE: QUERY PLAN:
Limit  (cost=0.00..1544.78 rows=1 width=49) (actual time=2299.11..2299.11 rows=0 loops=1)
  ->  Nested Loop  (cost=0.00..1266550.38 rows=820 width=49) (actual time=2299.10..2299.10 rows=0 loops=1)
        ->  Nested Loop  (cost=0.00..776978.04 rows=90825 width=42) (actual time=0.84..1849.97 rows=9939 loops=1)
              ->  Index Scan Backward using start_time_idx on call_results cr  (cost=0.00..6569.39 rows=6693 width=22) (actual time=0.38..303.58 rows=9043 loops=1)
              ->  Index Scan using full_phone_idx on lists l  (cost=0.00..114.94 rows=14 width=20) (actual time=0.15..0.16 rows=1 loops=9043)
        ->  Index Scan using area_code_idx on timezone tz  (cost=0.00..5.38 rows=1 width=7) (actual time=0.04..0.04 rows=0 loops=9939)
Total runtime: 2300.55 msec
maxTime (Records=1, Time=10ms)
SQL = 
select cr.start_time as max
				from call_results cr, timezone tz, lists l
				where (cr.start_time between '10/15/2002 08:00' and '10/15/2002 23:00')
				and l.full_phone = cr.phonenum
				and l.area_code = tz.area_code
				and tz.greenwich = '-8'
				and cr.project_id = 11
				and l.client_id = 8 
				order by cr.start_time desc
				limit 1
 NOTICE:  QUERY PLAN:
Limit  (cost=0.00..331.03 rows=1 width=49) (actual time=1.19..1.53 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..1266550.38 rows=3826 width=49) (actual time=1.19..1.52 rows=2 loops=1)
        ->  Nested Loop  (cost=0.00..776978.04 rows=90825 width=42) (actual time=0.84..1.10 rows=2 loops=1)
              ->  Index Scan Backward using start_time_idx on call_results cr  (cost=0.00..6569.39 rows=6693 width=22) (actual time=0.39..0.48 rows=2 loops=1)
              ->  Index Scan using full_phone_idx on lists l  (cost=0.00..114.94 rows=14 width=20) (actual time=0.30..0.30 rows=1 loops=2)
        ->  Index Scan using area_code_idx on timezone tz  (cost=0.00..5.38 rows=1 width=7) (actual time=0.19..0.20 rows=1 loops=2)
Total runtime: 1.74 msec
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2002-10-18 01:37:27 | Re: Max time queries | 
| Previous Message | Josh Berkus | 2002-10-15 18:00:58 | PG_Autotune 0.1 |