Thread: I: Help with indexes/queries/msaccess
Hi all...i'm converting an access db to postgres and i have a question on indexes/queries. The following query, when launched on postgres with the explain command, isn't using index scans but only sequential scans..why is this?? I've created all the necessary indexes but the query is very very slow... This is the query: SELECT tAnagraficaGen.*, Clienti.clingua, Clienti.cnote, tZone.tdescr, tPagamenti.tdescr as ptdescr, case when tAnagraficaGen.acodage>0 then tAnagraficaGen_1.araso else '' end AS AgentName FROM (tPagamenti RIGHT JOIN (tZone RIGHT JOIN (tAnagraficaGen LEFT JOIN Clienti ON tAnagraficaGen.aconto = Clienti.cidcliente) ON tZone.tcod=tAnagraficaGen.azona) ON tPagamenti.tcod=tAnagraficaGen.acodpag) LEFT JOIN tAnagraficaGen AS tAnagraficaGen_1 ON tAnagraficaGen.acodage=tAnagraficaGen_1.aconto; This is a query generated with access 97 and modified to work on postgres.. The query executed on postgres is much much slower than on access 97.. I was thinking of rewriting it with the "classic" join syntax eg: select * from a,b where a.a=b.b ...would this partially solve my problem?? Can anyone help me out.? Thanks in advance..! fabrizio@macrongolf.com http://macrongolf.com http://eteampoint.com http://macron.com
"Fabrizio Mazzoni" <fabrizio@macron.com> writes: > The following query, when launched on postgres with the > explain command, isn't using index scans but only sequential scans..why is > this? Have you done VACUUM ANALYZE? > I was thinking of rewriting it with the "classic" join syntax eg: select * > from a,b where a.a=b.b ...would this partially solve my problem?? If you actually need the outer-join behavior then you can't do that. However, it might help to rearrange the order in which the joins are done. See http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html regards, tom lane
When you use the join syntax you did, you're explicitly telling postgres in what order to join the tables. If you use the "classic" syntax as you described it, postgres will do its best to figure out the best join order. It probably couldn't hurt to try. When you say you created all the necessary indices, do you mean that all the fields used in the join criteria are indexed? "Fabrizio Mazzoni" <fabrizio%macron.com@interlock.lexmark.com> on 06/29/2001 09:47:01 AM To: pgsql-general%postgresql.org@interlock.lexmark.com cc: (bcc: Wesley Sheldahl/Lex/Lexmark) Subject: [GENERAL] I: Help with indexes/queries/msaccess Hi all...i'm converting an access db to postgres and i have a question on indexes/queries. The following query, when launched on postgres with the explain command, isn't using index scans but only sequential scans..why is this?? I've created all the necessary indexes but the query is very very slow... This is the query: [ query snipped] This is a query generated with access 97 and modified to work on postgres.. The query executed on postgres is much much slower than on access 97.. I was thinking of rewriting it with the "classic" join syntax eg: select * from a,b where a.a=b.b ...would this partially solve my problem?? Can anyone help me out.? Thanks in advance..! fabrizio@macrongolf.com http://macrongolf.com http://eteampoint.com http://macron.com ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org