Re: best way to query

Tom Lane wrote:
> Steve Clark <sclark@netwolves.com> writes:
>
>>explain shows:
>
>
>>  Aggregate  (cost=4712921585.30..4712921585.31 rows=1 width=0)
>>    ->  Seq Scan on t_event_ack_log a  (cost=103170.29..4712920878.60
>>rows=282677 width=0)
>>          Filter: (NOT (subplan))
>>          SubPlan
>>            ->  Materialize  (cost=103170.29..117301.92 rows=1016163
>>width=4)
>>                  ->  Index Scan using pk_tuel_eln on t_unit_event_log
>>  (cost=0.00..98184.12 rows=1016163 width=4)
>
>
> Yeah, that's going to suck.  A brute force solution is to see if you
> can get it to switch to a "hashed subplan" by increasing work_mem.
>
> Also, whatever is the ORDER BY for?
>
>             regards, tom lane
>

without the order by it wants to do a seq scan of t_unit_event_log.
see below:
  explain select count(*) from t_event_ack_log where event_log_no not
in (select event_log_no from t_unit_event_log);
                                        QUERY PLAN
----------------------------------------------------------------------------------------
  Aggregate  (cost=12144872193.82..12144872193.82 rows=1 width=0)
    ->  Seq Scan on t_event_ack_log  (cost=0.00..12144871485.07
rows=283497 width=0)
          Filter: (NOT (subplan))
          SubPlan
            ->  Seq Scan on t_unit_event_log  (cost=0.00..40286.56
rows=1021156 width=4)
(5 rows)


Will try increasing work_memory.