pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat constitutes a complex query
7+ messages / 5 participants
[nested] [flat]
* What constitutes a complex query
@ 2008-05-06 15:45 Justin <justin@emproshunts.com>
0 siblings, 3 replies; 7+ messages in thread
From: Justin @ 2008-05-06 15:45 UTC (permalink / raw)
To: pgsql-performance
This falls under the stupid question and i'm just curious what other
people think what makes a query complex?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What constitutes a complex query
@ 2008-05-06 16:26 Craig James <craig_james@emolecules.com>
parent: Justin <justin@emproshunts.com>
2 siblings, 1 reply; 7+ messages in thread
From: Craig James @ 2008-05-06 16:26 UTC (permalink / raw)
To: Justin <justin@emproshunts.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What constitutes a complex query
@ 2008-05-06 16:41 Scott Marlowe <scott.marlowe@gmail.com>
parent: Justin <justin@emproshunts.com>
2 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2008-05-06 16:41 UTC (permalink / raw)
To: Justin <justin@emproshunts.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What constitutes a complex query
@ 2008-05-06 16:56 Steve Atkins <steve@blighty.com>
parent: Justin <justin@emproshunts.com>
2 siblings, 0 replies; 7+ messages in thread
From: Steve Atkins @ 2008-05-06 16:56 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What constitutes a complex query
@ 2008-05-06 16:58 Richard Broersma <richard.broersma@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Broersma @ 2008-05-06 16:58 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Justin <justin@emproshunts.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What constitutes a complex query
@ 2008-05-06 17:23 Justin <justin@emproshunts.com>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 1 reply; 7+ messages in thread
From: Justin @ 2008-05-06 17:23 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: What constitutes a complex query
@ 2008-05-06 17:37 Scott Marlowe <scott.marlowe@gmail.com>
parent: Justin <justin@emproshunts.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2008-05-06 17:37 UTC (permalink / raw)
To: Justin <justin@emproshunts.com>; +Cc: Craig James <craig_james@emolecules.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-05-06 17:37 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-06 15:45 What constitutes a complex query Justin <justin@emproshunts.com>
2008-05-06 16:26 ` Craig James <craig_james@emolecules.com>
2008-05-06 17:23 ` Justin <justin@emproshunts.com>
2008-05-06 17:37 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-05-06 16:41 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-05-06 16:58 ` Richard Broersma <richard.broersma@gmail.com>
2008-05-06 16:56 ` Steve Atkins <steve@blighty.com>
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