By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,777 Members | 1,755 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,777 IT Pros & Developers. It's quick & easy.

Optimising SQL Queries?

P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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 YourEmailAddressHere" to ma*******@postgresql.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

iD8DBQFABNjJY5Twig3Ge+YRAgobAJ9zu48QvN3Pb1rmwupQNe QxOntbZgCgi8hc
MfqGnzG3uiaa+9uE32agK9k=
=+ukQ
-----END PGP SIGNATURE-----

Nov 22 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.