Re: Same conditions, different planning?

On Wed, Oct 22, 2003 at 04:13:45PM +0530, Shridhar Daithankar wrote:
> Pedro Alves wrote:
> > I'm having serious problems with a mission critical app that runs on
> >postgres (and has been running for the past 3 years). It's rather large,
> >and lately things are not going well. The planner is getting 'nuts',
> >choosing ways that never end and completly stops the database.
> > The strange thing is that running the same query with the same database
> >with the same conditions in other machine the planner works as expecter and
> >all goes well.
>
> Can we see explain analyze for the queries, relevant schema and other
> details?
>
> And what version of postgresql is this? On what platform?

Both machines Linux, 7.3.4 (different dists, but it doesn't matter, I
think)

Query:
SELECT ra.ra_id, ra.ra_reqnum, ra.ra_datacolh,to_char(timestamp_num,'YYYY-MM-DD'),to_char(timestamp_num,'HH24:MI'), ra.ra_servico, ra.ra_urgente, ra.ra_produto, ra.ra_cama, ra.ra_parcial, ra.ra_vglobal, servico.s_id, servico.s_desc ,ut.ut_id, ut.ut_nome, ut.ut_sexo,ut.ut_data_nasc,ra.ra_modulo, ra.ra_relcolh, ra.ra_notamedica,ra.ra_utilizador FROM Servico servico,RequisicaoAnalise ra, Utente ut WHERE ut.ut_id=ra.ra_utente AND ra.ra_servreq=servico.s_id and ra.ra_servico = 1 AND ra_datacolh <='2003-10-22' AND ra.isactive=1 order by ra_reqnum desc LIMIT 80 OFFSET 0;))

Machine 1 (production):

Limit (cost=2193.79..2193.99 rows=80 width=156)
-> Sort (cost=2193.79..2194.32 rows=212 width=156)
Sort Key: ra.ra_reqnum
-> Hash Join (cost=970.41..2185.62 rows=212 width=156)
Hash Cond: ("outer".ra_servreq = "inner".s_id)
-> Merge Join (cost=968.75..2180.25 rows=212 width=134)
Merge Cond: ("outer".ut_id = "inner".ra_utente)
-> Index Scan using utente_pkey on utente ut (cost=0.00..1109.18 rows=38937 width=43)
-> Sort (cost=968.75..969.97 rows=486 width=91)
Sort Key: ra.ra_utente
-> Index Scan using ra_isactive on
requisicaoanalise ra (cost=0.00..947.07 rows=486 width=91)
Index Cond: (isactive = 1)
Filter: ((ra_servico = 1) AND (ra_datacolh <= '2003-10-22'::date))
-> Hash (cost=1.53..1.53 rows=53 width=22)
-> Seq Scan on servico (cost=0.00..1.53 rows=53
width=22)
(15 rows)

Machine 2 (my develop machine):

Limit (cost=74.47..74.51 rows=19 width=156)
-> Sort (cost=74.47..74.51 rows=19 width=156)
Sort Key: ra.ra_reqnum
-> Hash Join (cost=1.66..74.05 rows=19 width=156)
Hash Cond: ("outer".ra_servreq = "inner".s_id)
-> Nested Loop (cost=0.00..72.05 rows=19 width=134)
-> Index Scan using ra_isactive on requisicaoanalise
ra (cost=0.00..5.09 rows=19 width=91)
Index Cond: (isactive = 1)
Filter: ((ra_servico = 1) AND (ra_datacolh <=
'2003-10-22'::date))
-> Index Scan using utente_pkey on utente ut (cost=0.00..3.43 rows=1 width=43)
Index Cond: (ut.ut_id = "outer".ra_utente)
-> Hash (cost=1.53..1.53 rows=53 width=22)
-> Seq Scan on servico (cost=0.00..1.53 rows=53
width=22)
(13 rows)

Some other relevant info:

select count(*) from requisicaoanalise;
count
--------
176328

select count(*) from utente;
count
-------
38868

Digging a bit more I find this:

Machine 1:

explain select count(*) from requisicaoanalise where isactive = 1 AND
(ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=948.45..948.45 rows=1 width=0)
-> Index Scan using ra_isactive on requisicaoanalise
(cost=0.00..947.07 rows=550 width=0)
Index Cond: (isactive = 1)
Filter: ((ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date))
(4 rows)

Machine 2:

explain select count(*) from requisicaoanalise where isactive = 1 AND
(ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date);
QUERY PLAN
--------------------------------------------------------------------------------------------
Aggregate (cost=5.12..5.12 rows=1 width=0)
-> Index Scan using ra_isactive on requisicaoanalise (cost=0.00..5.09
rows=12 width=0)
Index Cond: (isactive = 1)
Filter: ((ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date))

How can I have such different costs?!?!?!

--
Pedro Miguel G. Alves pmalves(at)think(dot)pt
THINK - Tecnologias de Informação www.think.pt
Tel: +351 21 413 46 00 Av. José Gomes Ferreira
Fax: +351 21 413 46 09 nº 13 1495-139 ALGÉS