By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,887 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

Same conditions, different planning?

P: n/a


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.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

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

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2

P: n/a
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 pm*****@think.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

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

Nov 12 '05 #3

P: n/a
On Wednesday 22 October 2003 16:25, Pedro Alves wrote:
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)


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

Nov 12 '05 #4

P: n/a


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 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)


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.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

---------------------------(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

Nov 12 '05 #5

P: n/a
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

Nov 12 '05 #6

P: n/a

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.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

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

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.