Thread: What constitutes a complex query
This falls under the stupid question and i'm just curious what other people think what makes a query complex?
Justin wrote: > This falls under the stupid question and i'm just curious what other > people think what makes a query complex? There are two kinds: 1. Hard for Postgres to get the answer. 2. Hard for a person to comprehend. Which do you mean? Craig
On Tue, May 6, 2008 at 9:45 AM, Justin <justin@emproshunts.com> wrote: > This falls under the stupid question and i'm just curious what other people > think what makes a query complex? Well, as mentioned, there's two kinds. some that look big and ugly are actually just shovelling data with no fancy interactions between sets. Some reporting queries are like this. I've made simple reporting queries that took up many pages that were really simple in nature and fast on even older pgsql versions (7.2-7.4) I'd say that the use of correlated subqueries qualifies a query as complicated. Joining on non-usual pk-fk stuff. the more you're mashing one set of data against another, and the odder the way you have to do it, the more complex the query becomes.
On May 6, 2008, at 8:45 AM, Justin wrote: > This falls under the stupid question and i'm just curious what other > people think what makes a query complex? If I know in advance exactly how the planner will plan the query (and be right), it's a simple query. Otherwise it's a complex query. As I get a better feel for the planner, some queries that used to be complex become simple. :) Cheers, Steve
On Tue, May 6, 2008 at 9:41 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > I'd say that the use of correlated subqueries qualifies a query as > complicated. Joining on non-usual pk-fk stuff. the more you're > mashing one set of data against another, and the odder the way you > have to do it, the more complex the query becomes. I would add that data analysis queries that have multiple level of aggregation analysis can be complicated also. For example, in a table of racer times find the average time for each team while only counting teams whom at least have greater than four team members and produce an ordered list displaying the ranking for each team according to their average time. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Craig James wrote: > Justin wrote: >> This falls under the stupid question and i'm just curious what other >> people think what makes a query complex? > > There are two kinds: > > 1. Hard for Postgres to get the answer. this one > > 2. Hard for a person to comprehend. > > Which do you mean? > > Craig >
On Tue, May 6, 2008 at 11:23 AM, Justin <justin@emproshunts.com> wrote: > > > Craig James wrote: > > > Justin wrote: > > > > > This falls under the stupid question and i'm just curious what other > people think what makes a query complex? > > > > > > > There are two kinds: > > > > 1. Hard for Postgres to get the answer. > > > this one Sometimes, postgresql makes a bad choice on simple queries, so it's hard to say what all the ones are that postgresql tends to get wrong. Plus the query planner is under constant improvement thanks to the folks who find poor planner choices and Tom for making the changes.