Same conditions, different planning?

[Pedro Alves]

Hi
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.
All is vaccumed analyzed.
What can cause this?
Tkx


--
Pedro Miguel G. Alves pm*****@think.p t
THINK - Tecnologias de Informação www.think.pt
Tel: [phone] Av. José Gomes Ferreira
Fax: [phone] nº 13 1495-139 ALGÉS

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Shridhar Daithankar]

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?

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Pedro Alves]

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(timesta mp_num,'YYYY-MM-DD'),to_char(ti mestamp_num,'HH 24: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.u t_data_nasc,ra. ra_modulo, ra.ra_relcolh, ra.ra_notamedic a,ra.ra_utiliza dor FROM Servico servico,Requisi caoAnalise ra, Utente ut WHERE ut.ut_id=ra.ra_ utente AND ra.ra_servreq=s ervico.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..2 185.62 rows=212 width=156)
Hash Cond: ("outer".ra_ser vreq = "inner".s_i d)
-> Merge Join (cost=968.75..2 180.25 rows=212 width=134)
Merge Cond: ("outer".ut_ id = "inner".ra_uten te)
-> Index Scan using utente_pkey on utente ut (cost=0.00..110 9.18 rows=38937 width=43)
-> Sort (cost=968.75..9 69.97 rows=486 width=91)
Sort Key: ra.ra_utente
-> Index Scan using ra_isactive on
requisicaoanali se 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.5 3 rows=53 width=22)
-> Seq Scan on servico (cost=0.00..1.5 3 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_ser vreq = "inner".s_i d)
-> Nested Loop (cost=0.00..72. 05 rows=19 width=134)
-> Index Scan using ra_isactive on requisicaoanali se
ra (cost=0.00..5.0 9 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.4 3 rows=1 width=43)
Index Cond: (ut.ut_id = "outer".ra_uten te)
-> Hash (cost=1.53..1.5 3 rows=53 width=22)
-> Seq Scan on servico (cost=0.00..1.5 3 rows=53
width=22)
(13 rows)

Some other relevant info:

select count(*) from requisicaoanali se;
count
--------
176328

select count(*) from utente;
count
-------
38868
Digging a bit more I find this:

Machine 1:

explain select count(*) from requisicaoanali se where isactive = 1 AND
(ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Aggregate (cost=948.45..9 48.45 rows=1 width=0)
-> Index Scan using ra_isactive on requisicaoanali se
(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 requisicaoanali se where isactive = 1 AND
(ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date);
QUERY PLAN
--------------------------------------------------------------------------------------------
Aggregate (cost=5.12..5.1 2 rows=1 width=0)
-> Index Scan using ra_isactive on requisicaoanali se (cost=0.00..5.0 9
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 pm*****@think.p t
THINK - Tecnologias de Informação www.think.pt
Tel: [phone] Av. José Gomes Ferreira
Fax: [phone] nº 13 1495-139 ALGÉS

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Shridhar Daithankar]

On Wednesday 22 October 2003 16:25, Pedro Alves wrote:

Machine 1:

explain select count(*) from requisicaoanali se where isactive = 1 AND
(ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date);
QUERY PLAN
---------------------------------------------------------------------------
-------------------- Aggregate (cost=948.45..9 48.45 rows=1 width=0)
-> Index Scan using ra_isactive on requisicaoanali se
(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)

Could you please run explain analyze rather than just explain for all the
things you posted earlier?

And are there any tuning parameters different on these two machines? RAM size
of HDD setup etc?

Are these two machine absolutely same from hardware and postgresql tuning
point of view?

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Pedro Alves]

As a final attempt I tryed a pg_dumpall; initdb; psql -f <file> . It
seems to work as expected.... (!)


On Wed, Oct 22, 2003 at 05:03:32PM +0530, Shridhar Daithankar wrote:

On Wednesday 22 October 2003 16:25, Pedro Alves wrote:

Machine 1:

explain select count(*) from requisicaoanali se where isactive = 1 AND
(ra_servico = 4) AND (ra_datacolh <= '2003-10-22'::date);
QUERY PLAN
---------------------------------------------------------------------------
-------------------- Aggregate (cost=948.45..9 48.45 rows=1 width=0)
-> Index Scan using ra_isactive on requisicaoanali se
(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)

Could you please run explain analyze rather than just explain for all the
things you posted earlier?

And are there any tuning parameters different on these two machines? RAM size
of HDD setup etc?

Are these two machine absolutely same from hardware and postgresql tuning
point of view?

Shridhar
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
Pedro Miguel G. Alves pm*****@think.p t
THINK - Tecnologias de Informação www.think.pt
Tel: [phone] Av. José Gomes Ferreira
Fax: [phone] nº 13 1495-139 ALGÉS

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Doug McNaught]

Pedro Alves <pm*****@think. pt> writes:

As a final attempt I tryed a pg_dumpall; initdb; psql -f <file> . It
seems to work as expected.... (!)

It may have been a case of index bloat--did you ever REINDEX? That
might have fixed it without a dump/reload.

7.4 has fixes for the index bloat problem.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Pedro Alves]

Hum... no, I didn't :/ I thought vacuum analyze would be enough. Next
time (neve, I hope) I will have that in mind

On Wed, Oct 22, 2003 at 08:36:10AM -0400, Doug McNaught wrote:

Pedro Alves <pm*****@think. pt> writes:

As a final attempt I tryed a pg_dumpall; initdb; psql -f <file> . It
seems to work as expected.... (!)

It may have been a case of index bloat--did you ever REINDEX? That
might have fixed it without a dump/reload.

7.4 has fixes for the index bloat problem.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Pedro Miguel G. Alves pm*****@think.p t
THINK - Tecnologias de Informação www.think.pt
Tel: [phone] Av. José Gomes Ferreira
Fax: [phone] nº 13 1495-139 ALGÉS

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings