pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Complex SQL query and performance strategy 2+ messages / 2 participants [nested] [flat]
* Complex SQL query and performance strategy @ 2002-10-09 10:32 Adam Witney <awitney@sghms.ac.uk> 2002-10-09 13:47 ` Re: Complex SQL query and performance strategy Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Adam Witney @ 2002-10-09 10:32 UTC (permalink / raw) To: pgsql-sql Hi, I have a complex SQL query which requires the joining of 18 tables. There are only primary key indices on the table and at the moment it runs a little slow (30s or so) and so I am trying to optimise it. The output of EXPLAIN is a little confusing and seems to vary from run to run. Does the query optimiser have trouble with larger number of table joins? Also this will be running from a web front end, and I hope to have it encapsulated all within a function. Would it be better to break it up into multiple SQL statements/functions? Or to try to really tweak the indices? Thanks for any advice adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Complex SQL query and performance strategy 2002-10-09 10:32 Complex SQL query and performance strategy Adam Witney <awitney@sghms.ac.uk> @ 2002-10-09 13:47 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2002-10-09 13:47 UTC (permalink / raw) To: Adam Witney <awitney@sghms.ac.uk>; +Cc: pgsql-sql Adam Witney <awitney@sghms.ac.uk> writes: > I have a complex SQL query which requires the joining of 18 tables. There > are only primary key indices on the table and at the moment it runs a little > slow (30s or so) and so I am trying to optimise it. > The output of EXPLAIN is a little confusing and seems to vary from run to > run. Does the query optimiser have trouble with larger number of table > joins? The output probably would vary, because at that number of tables it'll be using the GEQO optimizer, which is probabilistic. If you don't like that, you can raise the GEQO threshold above 18 tables, but I suspect you'll not like the amount of time the exhaustive optimizer will take. A reasonable solution is to jack up the threshold, experiment until you find a good query plan, and then restructure the query with explicit JOIN operators to limit the optimizer's search space. That will bring the planning time down out of the stratosphere. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html for details. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-10-09 13:47 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2002-10-09 10:32 Complex SQL query and performance strategy Adam Witney <awitney@sghms.ac.uk> 2002-10-09 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox