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

Strange Slow query

P: n/a
Hello all,

I have the following query that has a problem i cannot resolve:

SELECT puzzle_picking.*, tmagaztestate.mconto, tanagraficagen.araso, tmagaztestate.mdabol, tcausalimagaz.tdescr
FROM tcausalimagaz RIGHT JOIN (tanagraficagen RIGHT JOIN (tmagazrighe LEFT JOIN tmagaztestate ON (tmagazrighe.mnubol = tmagaztestate.mnubol) AND (tmagazrighe.mspecie = tmagaztestate.mspecie) AND (tmagazrighe.manno = tmagaztestate.manno) AND (tmagazrighe.mtiprk = tmagaztestate.mtiprk)) RIGHT JOIN puzzle_picking ON (tmagazrighe.mriga = puzzle_picking.pkriga) AND (tmagazrighe.mnubol = puzzle_picking.pknumbol) AND (tmagazrighe.mspecie = puzzle_picking.pkspecie) AND (tmagazrighe.manno = puzzle_picking.pkanno) ON tanagraficagen.aconto = tmagaztestate.mconto) ON tcausalimagaz.tcod = tmagazrighe.mcaus
WHERE (((puzzle_picking.pkartcode)='5320009'))
ORDER BY puzzle_picking.pkdate;

Basically it is very slow (20 secs to perform ), but if i change the right join of tanagraficagen to an inner join it is instant (< than a second). My question is:

Is it possible that a right join performs that much slower .. ? i mean i can undertsand it takes more time .. but 20 sec is far too much ..

What can i do to speed it up?? Keep in mind that i have indexes on all of the joined columns and on the column in the where clause ..

Thank you very much,

Fabrizio Mazzoni
Macron Srl
---------------------------(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 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
On Tuesday 10 February 2004 13:30, ve*******@libero.it wrote:
Hello all,

I have the following query that has a problem i cannot resolve:


Can I suggest reposting on the performance list?
You'll want to supply EXPLAIN ANALYSE output for the right-join and inner-join
after making sure you've vacuum analysed the tables in question.
Oh - version of PG is always useful too.

When you run the EXPLAIN ANALYSE look carefully for areas where the estimate
of number of rows is wrong.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 22 '05 #2

P: n/a
Please run it with EXPLAIN ANALYZE and post the results.

On Tue, Feb 10, 2004 at 02:30:57PM +0100, ve*******@libero.it wrote:
Hello all,

I have the following query that has a problem i cannot resolve:

SELECT puzzle_picking.*, tmagaztestate.mconto, tanagraficagen.araso, tmagaztestate.mdabol, tcausalimagaz.tdescr
FROM tcausalimagaz RIGHT JOIN (tanagraficagen RIGHT JOIN (tmagazrighe LEFT JOIN tmagaztestate ON (tmagazrighe.mnubol = tmagaztestate.mnubol) AND (tmagazrighe.mspecie = tmagaztestate.mspecie) AND (tmagazrighe.manno = tmagaztestate.manno) AND (tmagazrighe.mtiprk = tmagaztestate.mtiprk)) RIGHT JOIN puzzle_picking ON (tmagazrighe.mriga = puzzle_picking.pkriga) AND (tmagazrighe.mnubol = puzzle_picking.pknumbol) AND (tmagazrighe.mspecie = puzzle_picking.pkspecie) AND (tmagazrighe.manno = puzzle_picking.pkanno) ON tanagraficagen.aconto = tmagaztestate.mconto) ON tcausalimagaz.tcod = tmagazrighe.mcaus
WHERE (((puzzle_picking.pkartcode)='5320009'))
ORDER BY puzzle_picking.pkdate;

Basically it is very slow (20 secs to perform ), but if i change the right join of tanagraficagen to an inner join it is instant (< than a second). My question is:

Is it possible that a right join performs that much slower .. ? i mean i can undertsand it takes more time .. but 20 sec is far too much ..

What can i do to speed it up?? Keep in mind that i have indexes on all ofthe joined columns and on the column in the where clause ..

Thank you very much,

Fabrizio Mazzoni
Macron Srl


---------------------------(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
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAKT/nY5Twig3Ge+YRApflAJ4ug+uFC9Mji/krZmlaWhFFqQl5sQCgoUML
zeUkcR/gbBNqisGDY4awrNo=
=zHqG
-----END PGP SIGNATURE-----

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.