Re: partitioned table query question - Mailing list pgsql-general

From Gregory Stark
Subject Re: partitioned table query question
Date
Msg-id 871w9txmm3.fsf@oxford.xeocode.com
Whole thread Raw
In response to Re: partitioned table query question  (Erik Jones <erik@myemma.com>)
Responses Re: partitioned table query question
Re: partitioned table query question
List pgsql-general
"Erik Jones" <erik@myemma.com> writes:

> Well, given that the bin is computed as a function of some_id, the  most
> natural way would be to not have to mention that bin in SELECT  statements at
> all.

The problem Tom's tried to explain is that the function may or may not
preserve the bin. So for example if you wanted to bin based on the final digit
of a numeric number, so you had a constraint like

 CHECK substring(x::text, length(x::text)) = 0

And then you performed a query with something like "WHERE x = 1.0". The
constraint would appear to exclude all but bin 0. Whereas in fact it's
possible that records with the value "1" would appear in bin 1.

What's needed to make this work is some knowledge in the planner that the
numeric->text cast does not preserve the equality property of the numeric
operator class.

This would be the same information that would be needed to expression indexes
more useful. So if you had an expression index on "substring(name,1,3)" and
performed a query with a clause like "WHERE name = 'Gregory'" it could
intelligently perform an index scan on the key "Greg" and then recheck the key
"Gregory" against the table column.

The problem is that that's quite a lot of machinery. It's not just a boolean
flag for each function since there could be multiple "equals". Also you want
to know separately whether it preserves equality and whether it preserves the
entire btree ordering. So you potentially need a whole new table with every
combination of btree operator class and function and several boolean columns
for each combination.

> However, it does appear that either a.) including the bin as a table
> attribute and in the where clause (either directly or the computation) or
> b.) precomputing the bin and directly accessing the child table will be the
> only options we have for now.

Or the near future.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

pgsql-general by date:

Previous
From: "Mason Hale"
Date:
Subject: Re: partitioned table query question
Next
From: "Mason Hale"
Date:
Subject: Using hashtext and unique constraints together