Re: [PERFORM] Better Hardware, worst Results
Em Qui, 2004-11-04 às 20:58, Rod Taylor escreveu: > All 3 plans have crappy estimates. > > Run ANALYZE in production, then send another explain analyze (as an > attachment please, to avoid linewrap). First of all, I'd like to apoligize for taking so long to post a new position. After this, I apologize again because the problem was in my query. It used some functions that for some reason made the Dell machine have a greater cost than our house-made machine. After correcting this functions, the results were faster in the Dell machine. The last apologize is for the linewrapped explains. In our brazilian PostgreSQL mailing list, attachments are not allowed, so I send them as inline text. Thanks to everyone who spent some time to help me solving this problem. -- +---+ | Alvaro Nunes MeloAtua Sistemas de Informacao | | [EMAIL PROTECTED]www.atua.com.br | |UIN - 42722678(54) 327-1044| +---+ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Better Hardware, worst Results
All 3 plans have crappy estimates. Run ANALYZE in production, then send another explain analyze (as an attachment please, to avoid linewrap). Er, no other possible answer except Rod's :-) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Better Hardware, worst Results
On Thu, 2004-11-04 at 17:42, [EMAIL PROTECTED] wrote: > Citando Rod Taylor <[EMAIL PROTECTED]>: > > Please send an explain analyze from both. > I'm sendin three explains. In the first the Dell machine didn't use existing > indexes, so I turn enable_seqscan off (this is the second explain). The total > cost decreased, but the total time not. The third explain refers to the cheaper > (and faster) machine. The last thing is the query itself. All 3 plans have crappy estimates. Run ANALYZE in production, then send another explain analyze (as an attachment please, to avoid linewrap). ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Better Hardware, worst Results
[EMAIL PROTECTED] wrote: Citando Rod Taylor <[EMAIL PROTECTED]>: Please send an explain analyze from both. I'm sendin three explains. In the first the Dell machine didn't use existing indexes, so I turn enable_seqscan off (this is the second explain). The total cost decreased, but the total time not. The third explain refers to the cheaper (and faster) machine. The last thing is the query itself. Nested Loop (cost=9008.68..13596.97 rows=1 width=317) (actual time=9272.803..65287.304 rows=2604 loops=1) Nested Loop (cost=5155.51..19320.20 rows=1 width=317) (actual time=480.311..62530.121 rows=2604 loops=1) Hash Join (cost=2.23..11191.77 rows=9 width=134) (actual time=341.708..21868.167 rows=2604 loops=1) Well the plan is completely different on the dev machine. Therefore either the PG version or the postgresql.conf is different. No other possible answer. M
Re: [PERFORM] Better Hardware, worst Results
Citando Rod Taylor <[EMAIL PROTECTED]>:
> Please send an explain analyze from both.
I'm sendin three explains. In the first the Dell machine didn't use existing
indexes, so I turn enable_seqscan off (this is the second explain). The total
cost decreased, but the total time not. The third explain refers to the cheaper
(and faster) machine. The last thing is the query itself.
Nested Loop (cost=9008.68..13596.97 rows=1 width=317) (actual
time=9272.803..65287.304 rows=2604 loops=1)
-> Hash Join (cost=9008.68..13590.91 rows=1 width=319) (actual
time=9243.294..10560.330 rows=2604 loops=1)
Hash Cond: ("outer".cd_tipo_pagamento = "inner".cd_tipo_pagamento)
-> Hash Join (cost=9007.59..13589.81 rows=1 width=317) (actual
time=9243.149..10529.765 rows=2604 loops=1)
Hash Cond: ("outer".cd_condicao = "inner".cd_condicao)
-> Nested Loop (cost=9006.46..13588.62 rows=8 width=315)
(actual time=9243.083..10497.385 rows=2604 loops=1)
-> Merge Join (cost=9006.46..13540.44 rows=8 width=290)
(actual time=9242.962..10405.245 rows=2604 loops=1)
Merge Cond: ("outer".cd_pessoa = "inner".cd_pessoa)
-> Nested Loop Left Join (cost=4658.37..9183.72
rows=375 width=286) (actual time=9210.101..10327.003 rows=23392 loops=1)
-> Merge Left Join (cost=4658.37..6924.15
rows=375 width=274) (actual time=9209.952..9981.475 rows=23392 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Merge Left Join
(cost=3366.00..5629.19 rows=375 width=255) (actual time=9158.705..9832.781
rows=23392 loops=1)
Merge Cond: ("outer".cd_pessoa =
"inner".cd_pessoa)
-> Nested Loop Left Join
(cost=2073.63..4334.24 rows=375 width=236) (actual time=8679.698..9152.213
rows=23392 loops=
1)
-> Merge Left Join
(cost=2073.63..2075.94 rows=375 width=44) (actual time=8679.557..8826.898
rows=23392 loops=1
)
Merge Cond:
("outer".cd_pessoa = "inner".cd_pessoa)
-> Sort
(cost=1727.15..1728.09 rows=375 width=40) (actual time=8580.391..8611.842
rows=23392 loops=1)
Sort Key:
p.cd_pessoa
-> Seq Scan on
pessoa p (cost=0.00..1711.12 rows=375 width=40) (actual time=0.371..8247.028
rows=50
412 loops=1)
Filter:
(cliente_liberado(cd_pessoa) = 1)
-> Sort
(cost=346.47..346.69 rows=85 width=8) (actual time=99.121..120.706 rows=16470
loops=1)
Sort Key:
e.cd_pessoa
-> Seq Scan on
endereco e (cost=0.00..343.75 rows=85 width=8) (actual time=0.070..30.558
rows=16858
loops=1)
Filter:
(id_tipo_endereco = 2)
-> Index Scan using
pk_pessoa_juridica on pessoa_juridica pj (cost=0.00..6.01 rows=1 width=196)
(actual time=0.
007..0.008 rows=1 loops=23392)
Index Cond:
(pj.cd_pessoa = "outer".cd_pessoa)
-> Sort (cost=1292.37..1293.18
rows=325 width=23) (actual time=478.963..522.701 rows=33659 loops=1)
Sort Key: t.cd_pessoa
-> Seq Scan on telefone t
(cost=0.00..1278.81 rows=325 width=23) (actual time=0.039..120.256 rows=59572
loops=1
)
Filter: (id_principal =
1::smallint)
-> Sort (cost=1292.37..1293.18 rows=325
width=23) (actual time=51.205..53.662 rows=3422 loops=1)
Sort Key: tf.cd_pessoa
-> Seq Scan on telefone tf
(cost=0.00..1278.81 rows=325 width=23) (actual time=0.024..43.192 rows=3885
loops=1)
Filter: (id_tipo =
4::smallint)
-> Index Scan using pk_cep on cep c
(cost=0.00..6.01 rows=1 width=20) (actual time=0.007..0.009 rows=1 loops=23392)
Index Cond: (c.cd_cep = "outer".cd_cep)
-> Sort (cost=4348.08..4351.89 rows=1524 width=4)
(actual time=13.182..18.069 rows=2619 loops=1)
Sort Key: cgv.cd_pessoa
Re: [PERFORM] Better Hardware, worst Results
Alvaro Nunes Melo <[EMAIL PROTECTED]> writes: > I have a very tricky situation here. A client bought a Dell dual-machine > to be used as Database Server, and we have a cheaper machine used in > development. With identical databases, configuration parameters and > running the same query, our machine is almost 3x faster. > ==> Dell PowerEdge: > HD: SCSI > ==> Other machine: > HD: IDE I'll bet a nickel that the IDE drive is lying about write completion, thereby gaining a significant performance boost at the cost of probable data corruption during a power failure. SCSI drives generally tell the truth about this, but consumer-grade IDE gear is usually configured to lie. regards, tom lane ---(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
Re: [PERFORM] Better Hardware, worst Results
On Thu, 2004-11-04 at 16:06, Alvaro Nunes Melo wrote: > Hi, > > I have a very tricky situation here. A client bought a Dell dual-machine > to be used as Database Server, and we have a cheaper machine used in > development. With identical databases, configuration parameters and > running the same query, our machine is almost 3x faster. Please send an explain analyze from both. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Better Hardware, worst Results
Hi, I have a very tricky situation here. A client bought a Dell dual-machine to be used as Database Server, and we have a cheaper machine used in development. With identical databases, configuration parameters and running the same query, our machine is almost 3x faster. I tried to increase the shared_buffers and other parameters, but the result is still the same. I would like to know what can I do to check what can be "holding" the Dell server (HD, memory, etc). Both machines run Debian Linux. I'll post configuration details below, so you'll can figure my scenario better. ==> Dell PowerEdge: HD: SCSI processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Xeon(TM) CPU 2.80GHz stepping: 9 cpu MHz : 2791.292 cache size : 512 KB processor : 1 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Xeon(TM) CPU 2.80GHz stepping: 9 cpu MHz : 2791.292 cache size : 512 KB # free -m total used free sharedbuffers cached Mem: 1010996 14 0 98 506 ==> Other machine: HD: IDE processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Pentium(R) 4 CPU 2.26GHz stepping: 5 cpu MHz : 2262.166 cache size : 512 KB #free -m total used free sharedbuffers cached Mem: 439434 4 0 16 395 -- +---+ | Alvaro Nunes MeloAtua Sistemas de Informacao | | [EMAIL PROTECTED]www.atua.com.br | |UIN - 42722678(54) 327-1044| +---+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
