Lists: | pgsql-general |
---|
From: | "Matteo Bertini" <matteob(at)naufraghi(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | hint unique result fro union |
Date: | 2006-08-16 15:23:16 |
Message-ID: | 1e2c38000608160823x70db6813l2cec3e45dca9372e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello all!
I'm quite new to pg, but I'm using it quite a lot in the last few monts.
Deeping in new features, I found a question: is it possible to hint an
unique result from a select?
Mainly, thinking about partitions, I'd like to create a small, frequently
accessed partition and a big, rarely accessed partition.
I'd like to hint pg to stop the query on the parent partition at the first
found item (and so hint not to analyze all the childs), because I know the
index I'm using is unique.
Possible? Usefull?
Thanks,
Matteo Bertini
From: | Steve Atkins <steve(at)blighty(dot)com> |
---|---|
To: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: hint unique result fro union |
Date: | 2006-08-16 17:51:31 |
Message-ID: | 4BA6658F-7925-4D5A-B121-2CC30088D427@blighty.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Aug 16, 2006, at 8:23 AM, Matteo Bertini wrote:
> Hello all!
>
> I'm quite new to pg, but I'm using it quite a lot in the last few
> monts.
>
> Deeping in new features, I found a question: is it possible to hint
> an unique result from a select?
>
> Mainly, thinking about partitions, I'd like to create a small,
> frequently accessed partition and a big, rarely accessed partition.
>
> I'd like to hint pg to stop the query on the parent partition at
> the first found item (and so hint not to analyze all the childs),
> because I know the index I'm using is unique.
>
> Possible? Usefull?
>
"select foo from bar limit 1" ?
I don't know if there's any guaranteed ordering of results from
a union query, though, and that's what a query on a set of
inherited tables will expand to, pretty much.
Cheers,
Steve
From: | "Jaime Casanova" <systemguards(at)gmail(dot)com> |
---|---|
To: | "Matteo Bertini" <matteob(at)naufraghi(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: hint unique result fro union |
Date: | 2006-08-16 18:31:13 |
Message-ID: | c2d9e70e0608161131n5e78d04aw1af103869ebc8902@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> Mainly, thinking about partitions, I'd like to create a small, frequently
> accessed partition and a big, rarely accessed partition.
>
> I'd like to hint pg to stop the query on the parent partition at the first
> found item (and so hint not to analyze all the childs), because I know the
> index I'm using is unique.
>
what you want is know as "Constraint Exclusion"
http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
From: | Matteo Bertini <matteo(at)naufraghi(dot)net> |
---|---|
To: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: hint unique result from union |
Date: | 2006-08-16 20:26:10 |
Message-ID: | 44E37F62.8000608@naufraghi.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Correct! Didn't noticed the "never executed" in the explain analyze
output :-P
Steve Atkins ha scritto:
>
>
> "select foo from bar limit 1" ?
>
> I don't know if there's any guaranteed ordering of results from
> a union query, though, and that's what a query on a set of
> inherited tables will expand to, pretty much.
>
> Cheers,
> Steve
>
Attachment | Content-Type | Size |
---|---|---|
matteo.vcf | text/x-vcard | 354 bytes |
From: | "Matteo Bertini" <matteob(at)naufraghi(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: hint unique result fro union |
Date: | 2006-08-25 18:48:43 |
Message-ID: | 1e2c38000608251148j44449235wd585244c62f0fc16@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Yes, the "LIMIT 1" is OK, thanks!
I didn't noticed the "not executed" in the explain output!
I have done some test comparing a plain table and a partitioned table
performance in a particular statistical hypothesis (something explained here
too: http://tagschema.com ).
Mainly, data is produced and asked following an exponential distribution. So
there a few very frequently accessed items and a lot of uncommon items.
Partitioning the two sets in different tables, the performance gain is
interesting only after a few thousand rows.
Sorry, the text is Italian, the code (sql schema and python test code) will
be released in a few days (and you will need plpython from CVS if you are
using 8.1), nevertheless pictures are already self explaining :-P
... I have seen a similar benchmark online but I cannot find it now to
compare.
Sorry for my English, and open to comment about my simple benchmark,
Matteo Bertini
2006/8/16, Steve Atkins <steve(at)blighty(dot)com>:
>
>
> On Aug 16, 2006, at 8:23 AM, Matteo Bertini wrote:
>
> > Hello all!
> >
> > I'm quite new to pg, but I'm using it quite a lot in the last few
> > monts.
> >
> > Deeping in new features, I found a question: is it possible to hint
> > an unique result from a select?
> >
> > Mainly, thinking about partitions, I'd like to create a small,
> > frequently accessed partition and a big, rarely accessed partition.
> >
> > I'd like to hint pg to stop the query on the parent partition at
> > the first found item (and so hint not to analyze all the childs),
> > because I know the index I'm using is unique.
> >
> > Possible? Usefull?
> >
>
> "select foo from bar limit 1" ?
>
> I don't know if there's any guaranteed ordering of results from
> a union query, though, and that's what a query on a set of
> inherited tables will expand to, pretty much.
>
> Cheers,
> Steve
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>