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 majordomo@postgresql.org)
--
Martijn van Oosterhout <kleptog@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
