Poor Plan selected w/ not provided a date/time but selecting date/time from a table - Mailing list pgsql-general

Query2 is way faster mainly because the plan does not choose a seq scan
on a table w/ >20million rows.
The only difference between Query 1 and query 2 is that the
audio_key_dtime is chosen from a table rather than provided on the
query.

I'm not sure why this is the case and why it chooses such plans.
(should I be posting to pg-performance?)


Query1
=====

select
foo,
bar,
foobar
from
A,
join B
on a.id = b.id
join C
on c.id = b.id
and c.start_dtime = b.start_dtime
where audit_key_dtime >= (select last_refreshed from denorm_log where tablename = 'zon')
and   audit_key_dtime <  (select last_refreshed + refresh_interval from denorm_log where tablename = 'zon')

Query2
=====

select
foo,
bar,
foobar
from
A,
join B
on a.id = b.id
join C
on c.id = b.id
and c.start_dtime = b.start_dtime
where audit_key_dtime >= '2007-08-08 18:00:00'
and   audit_key_dtime <  '2007-08-08 18:01:00'


structure of denorm_log (very small table, max 10 rows)
=======================

                        Table "xmms.denorm_log"
         Column          |            Type             |   Modifiers
-------------------------+-----------------------------+---------------
 table_name              | text                        | not null
 denorm_table_name       | text                        | not null
 last_refreshed          | timestamp without time zone |
 refresh_interval        | interval                    |
 record_update_date_time | timestamp without time zone | default now()
 sql_delete              | text                        |
 sql_insert              | text                        |
Indexes:
    "denorm_log_pkey" PRIMARY KEY, btree (table_name)



Plan 1
======

HashAggregate  (cost=478784.63..478784.92 rows=1 width=139) (actual time=60593.583..60601.532 rows=82 loops=1)
  InitPlan
    ->  Index Scan using denorm_log_pkey on denorm_log  (cost=0.00..8.27 rows=1 width=8) (actual time=0.040..0.045
rows=1loops=1) 
          Index Cond: (table_name = 'zon'::text)
    ->  Index Scan using denorm_log_pkey on denorm_log  (cost=0.00..8.27 rows=1 width=24) (actual time=0.015..0.016
rows=1loops=1) 
          Index Cond: (table_name = 'zon'::text)
  ->  Nested Loop  (cost=387334.78..478768.01 rows=1 width=139) (actual time=46024.153..59479.035 rows=1545 loops=1)
        ->  Hash Join  (cost=387334.78..478759.22 rows=1 width=109) (actual time=45999.776..58651.991 rows=1545
loops=1)
              Hash Cond: (((trz.id)::text = (ts.id)::text) AND (trz.start_dtime = ts.start_dtime))
              ->  Index Scan using idx_trz_uptime on zon trz  (cost=0.00..3252.87 rows=65008 width=54) (actual
time=25.905..240.211rows=1545 loops=1) 
                    Index Cond: ((audit_key_dtime >= $0) AND (audit_key_dtime < $1))
                    Filter: (pber_err_rate_hd_zn_2 <> 0::numeric)
              ->  Hash  (cost=242589.91..242589.91 rows=5518191 width=63) (actual time=45873.643..45873.643
rows=5518191loops=1) 
                    ->  Seq Scan on ts  (cost=0.00..242589.91 rows=5518191 width=63) (actual time=355.084..28490.120
rows=5518191loops=1) 
        ->  Index Scan using d_pkey on drive d  (cost=0.00..8.70 rows=1 width=62) (actual time=0.506..0.510 rows=1
loops=1545)
              Index Cond: ((d.id)::text = (trz.id)::text)
Total runtime: 60607.891 ms


Plan 2 - No Seq Scans
======

HashAggregate  (cost=28.16..28.46 rows=1 width=139) (actual time=2240.899..2248.964 rows=82 loops=1)
  ->  Nested Loop  (cost=0.00..28.08 rows=1 width=139) (actual time=485.178..1175.938 rows=1545 loops=1)
        ->  Nested Loop  (cost=0.00..19.29 rows=1 width=109) (actual time=485.032..1125.322 rows=1545 loops=1)
              ->  Index Scan using idx_trz_uptime on zon trz  (cost=0.00..9.70 rows=1 width=54) (actual
time=24.481..33.667rows=1545 loops=1) 
                    Index Cond: ((audit_key_dtime >= '2007-08-08 18:00:00'::timestamp without time zone) AND
(audit_key_dtime< '2007-08-08 18:01:00'::timestamp without time zone)) 
                    Filter: (web_id <> 0::numeric)
              ->  Index Scan using idx_ts_sn_sdate_ttype on ts  (cost=0.00..9.58 rows=1 width=63) (actual
time=0.696..0.698rows=1 loops=1545) 
                    Index Cond: (((ts.id)::text = (trz.id)::text) AND (ts.start_dtime = trz.start_dtime))
        ->  Index Scan using d_pkey on  d  (cost=0.00..8.70 rows=1 width=62) (actual time=0.012..0.015 rows=1
loops=1545)
              Index Cond: ((d.id)::text = (trz.id)::text)
Total runtime: 2250.075 ms