Optimising SQL Queries?

[Alex Satrapa]

Is there any (simple? har!) way to optimise a particular SQL query?

At this stage, I'm more interested in making the query more readable, so
I've started hunting down references about relational theory to see if I
can use relational algebra to manipulate the query into a "shorter" form
(I believe the mathematical term is "simplify") .

Since I've got somewhere between two days and two lifetimes' worth of
reading ahead of me, would it be too presumptious to ask the list if
anyone can recommend a method, tool or book that would help me out?

At present, the one query I'm struggling with is a mish-mash of unions,
uniques and subselects. I'm sure there's an easier way, but I just can't
get the tables in my head to morph themselves the way I want them to.
The "explain" of my query is 38 rows long!

In my perfect world, there'd be some tool available where I could
provide the table definitions and my sample query, at which time the
tool would spit out a magically optimised query that provides exactly
the same results with far fewer linear scans, subselects and unique
filters. Heck... perhaps it could even suggest what indices I should add
to my tables to improve the query plan.

But it's not a perfect world, and I'm just a clueless newbie in the land
of relational theory, with no idea of the caveats that cover trying to
apply relational theory to real-world SQL!

Any suggestions on general strategies in producing sql queries? eg: is
it better to apply filters closer to the data or closer to the end
result table ("deeper" or "shallower" in the subselect tree)?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Martijn van Oosterhout]

Hmm, relational algebra is not always straight, and anyway, many people
complain that SQL doesn't really conform to relational algebra anyway.

The usual things to check are (off the top of my head):

* Are you using UNION where UNION ALL would be more appropriate (saves a
unique)
* Are the subselects that can be pulled into joins (postgresql is pretty
good with these).
* Are you using IN on an older version of PostgreSQL.
* Are there joins with differing types that could cause non-optimal use of
indexes.
* Partial indexes

Remember, just because PostgreSQL isn't using the index now doesn't mean it
won't when a table gets to a significant size.

Hope this helps,

On Wed, Jan 14, 2004 at 03:03:45PM +1100, Alex Satrapa wrote:

Is there any (simple? har!) way to optimise a particular SQL query?

At this stage, I'm more interested in making the query more readable, so
I've started hunting down references about relational theory to see if I
can use relational algebra to manipulate the query into a "shorter" form
(I believe the mathematical term is "simplify") .

Since I've got somewhere between two days and two lifetimes' worth of
reading ahead of me, would it be too presumptious to ask the list if
anyone can recommend a method, tool or book that would help me out?

At present, the one query I'm struggling with is a mish-mash of unions,
uniques and subselects. I'm sure there's an easier way, but I just can't
get the tables in my head to morph themselves the way I want them to.
The "explain" of my query is 38 rows long!

In my perfect world, there'd be some tool available where I could
provide the table definitions and my sample query, at which time the
tool would spit out a magically optimised query that provides exactly
the same results with far fewer linear scans, subselects and unique
filters. Heck... perhaps it could even suggest what indices I should add
to my tables to improve the query plan.

But it's not a perfect world, and I'm just a clueless newbie in the land
of relational theory, with no idea of the caveats that cover trying to
apply relational theory to real-world SQL!

Any suggestions on general strategies in producing sql queries? eg: is
it better to apply filters closer to the data or closer to the end
result table ("deeper" or "shallower" in the subselect tree)?



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
--
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

iD8DBQFABNjJY5T wig3Ge+YRAgobAJ 9zu48QvN3Pb1rmw upQNeQxOntbZgCg i8hc
MfqGnzG3uiaa+9u E32agK9k=
=+ukQ
-----END PGP SIGNATURE-----