From: Josh Berkus <josh@agliodbs.com>
To: Dav Coleman <dav@danger-island.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Query Optimization
Date: Thu, 18 Apr 2002 10:38:16 -0700
Message-ID: <web-1376198@davinci.ethosmedia.com> (raw)
In-Reply-To: <20020418093527.I24932@danger-island.com>
Dav,
> I should be more clear, the problem is that the application user can
> basically construct the SQL query dynamically, so I have no control
> on
> how the original SQL query will be formed or what it will consist of.
> It can be any possible query in practice. Because of this, it is not
> just
> a matter of analyzing any specific queries, and i don't want to start
> creating every possible index (although i might, if i have to).
See Tom's response. He's the expert.
However, if the user is allowed to write any query they wish, it does
sound like you'll need to construct every reasonable index. This will
make UPDATES on your tables very expensive, but you have no way of
anticipating what the user will ask.
You'll also need to take a really hard look at the relational structure
of your database. Seemingly trivial lack of Normal Form in your table
structures can become very costly as far as subqueries are concerned.
Also, DISTINCT can be very costly on large tables.
> I just figured I couldn't be the first person to run into this
> problem,
> but I can't find it mentioned anywhere.
Good luck. I can't even think of any books I've reveiwed that would
address this issue. Part of the problem, I think, is that optimization
is so circumstantial.
> btw, I'm running postgresql-7.1.2 (compilied from source) on rh7.0
I very much suggest that you upgrade to 7.2.1. Tom and company have
made substantial improvements to the query parser and the tracking of
index statistics in 7.2.
-Josh
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, dav@danger-island.com
Subject: Re: SQL Query Optimization
In-Reply-To: <web-1376198@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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