Search Postgresql Archives

Re: Evil Nested Loops

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 2009-06-03 at 01:28 -0600, Scott Marlowe wrote:
> On Wed, Jun 3, 2009 at 12:32 AM, Ow Mun Heng <Ow.Mun.Heng@xxxxxxx> wrote:
> > HashAggregate  (cost=8035443.21..8035445.17 rows=157 width=24)
> >  ->  Nested Loop  (cost=37680.95..7890528.72 rows=28982898 width=24) <<<<< suspect
> >        Join Filter: ((a.test_run_start_date_time >= date.start_time) AND (a.test_run_start_date_time <= date.end_time))
> >        ->  Bitmap Heap Scan on d_trh_pbert a  (cost=37677.22..1369372.99 rows=1661440 width=24)
> >              Recheck Cond: ((test_run_start_date_time >= '2009-05-08 07:00:00'::timestamp without time zone) AND (test_run_start_date_time <= '2009-05-15 06:59:59'::timestamp without time zone))
> >              ->  Bitmap Index Scan on idx_d_trh_pbert_sdate  (cost=0.00..37261.86 rows=1661440 width=0)
> >                    Index Cond: ((test_run_start_date_time >= '2009-05-08 07:00:00'::timestamp without time zone) AND (test_run_start_date_time <= '2009-05-15 06:59:59'::timestamp without time zone))
> >        ->  Materialize  (cost=3.73..5.30 rows=157 width=24)
> >              ->  Seq Scan on lookup_ww_date2 date  (cost=0.00..3.57 rows=157 width=24)

> OK, looking at your query and the plan, what you're doing is kind of this:
> 
> 157 Rows times 1661440 Rows (cross product) = 260M or so and then you
> filter out the 157 original rows and their matches.  Note that an
> explain ANALYZE might shed more light, but given the high cost in this
> query for the nested loop I'm guessing the only thing you can do is
> throw more work_mem at it.  But it's fundamentally flawed in design I
> think.

The explain analyze runs >10 mins and then I just aborted it.

WW49 is basically between 5/8 and 5/15, unfortunately, it's not actually
just the base dates, it's also the time.

eg: 200949|5/8/2009 7:00:00am|5/15/2009 6:59:59AM

The definition of  WW or a "day" is actually between 
eg: 5/8 7am to 5/9 6:59:59am


> If you're always working with dates maybe joining on
> date_trunc('day',test_run_start_date)=date_trunc('day',startdate')
> with an index on both terms will work?

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux