Re: Big diference in response time (query plan question)

Hi Dave,
Thanks to reply.
I run it now in a Postgres 8.1.4  my notebook (win XP) and the
performance is really much better:

EXPLAIN ANALYZE
SELECT   Contrato.Id
     , Min( prog.dtsemeio   ) AS DtSemIni
     , Max( prog.dtsemeio   ) AS DtSemFim
     , Min( prog.dtembarque ) AS DtEmbIni
     , Max( prog.dtembarque ) AS DtEmbFim
     , Min( prog.dtentrega  ) AS DtEntIni
     , Max( prog.dtentrega  ) AS DtEntFim
     , COUNT(prog.*) AS QtSem
     , SUM( CASE   WHEN Prog.DtSemeio >= '20060814' THEN 1 ELSE 0 END )
AS QtSemAb
FROM     bvz.Contrato
       LEFT OUTER JOIN bvz.Prog ON prog.Fk_Contrato = Contrato.Id
WHERE    Contrato.Fk_Clifor = 243
GROUP BY 1;

GroupAggregate  (cost=2.18..7312.45 rows=42 width=48) (actual
time=0.446..13.195 rows=42 loops=1)
  ->  Nested Loop Left Join  (cost=2.18..7291.22 rows=883 width=48)
(actual time=0.103..10.518 rows=1536 loops=1)
        ->  Index Scan using pk_contrato on contrato  (cost=0.00..100.29
rows=42 width=4) (actual time=0.048..3.163 rows=42 loops=1)
              Filter: (fk_clifor = 243)
        ->  Bitmap Heap Scan on prog  (cost=2.18..170.59 rows=50
width=48) (actual time=0.027..0.132 rows=37 loops=42)
              Recheck Cond: (prog.fk_contrato = "outer".id)
              ->  Bitmap Index Scan on fki_prog_contrato
(cost=0.00..2.18 rows=50 width=0) (actual time=0.018..0.018 rows=37
loops=42)
                    Index Cond: (prog.fk_contrato = "outer".id)
Total runtime: 13.399 ms

Where I can see the current random_page_cost value ? There are some hint
about what value I must set ?
Thanks in advance.
Luiz

Dave Dutcher wrote:
>> Well, in this case the queries with LEFT OUTER join and with
>> inner join
>> returns the same result set. I don´t have the sufficient knowledge to
>> affirm , but I suspect that if the query plan used for
>> fk_clifor = 352
>> and with left outer join is applied for the first query
>> (fk_clifor = 243
>> with left outer join)
>> we will have a better total runtime.
>> There are some manner to make this test ?
>>
>
> It looks like Postgres used a nested loop join for the fast query and a
> merge join for the slow query.  I don't think the left join is causing any
> problems.  On the slower query the cost estimate of the nested loop must
> have been higher than the cost estimate of the merge join because of more
> rows.  You could try disabling merge joins with the command "set
> enable_mergejoin=false".  Then run the explain analyze again to see if it is
> faster.
>
> If it is faster without merge join, then you could try to change your
> settings to make the planner prefer the nested loop.  I'm not sure what the
> best way to do that is.  Maybe you could try reducing the random_page_cost,
> which should make index scans cheaper.
>
> Dave
>