Thread: hint unique result fro union

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

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

> 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

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
>

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.

http://www.slug.it/pgsqlpart

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