Обсуждение: Strange Slow query

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
isinstant (< 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
secis 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
whereclause .. 

Thank you very much,

Fabrizio Mazzoni
Macron Srl

On Tuesday 10 February 2004 13:30, veramente@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

Please run it with EXPLAIN ANALYZE and post the results.

On Tue, Feb 10, 2004 at 02:30:57PM +0100, veramente@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
isinstant (< 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
secis 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
whereclause .. 
>
> 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 majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

--
Martijn van Oosterhout   <kleptog@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