Re: effective SELECT from child tables

On Fri, Sep 30, 2005 at 09:54:39PM +0100, Simon Riggs wrote:
> On Wed, 2005-09-28 at 22:24 +0200, Martijn van Oosterhout wrote:
> > I wonder if it would be possible to tweak the constraints exclusion
> > code so that if it sees something of the form "tableoid = X" to exclude
> > other tables...
> > You know, assume each table has a constraint "tableoid = OID".
> > Still, it is a fairly unusual feature.
> As I pointed out, the solution I proposed works with CE, in comparison
> with selecting a tableoid, which does not. It also costs 1 byte per row.

I can't see the use of an extra char column in a row, that is static
for an entire table, as anything except an ugly hack. It might work.
It doesn't make it right.

> Jim Nasby requested a similar feature a couple of months back.
> Essentially this request reduces to the idea of having attributes that
> are constant for all rows of a table. That doesn't have any side or
> additional benefits AFAICS, so improving that case isn't going to be at
> the top of a worthwhile-improvements list for a long time yet,
> especially since the CE list already has at least 10 items on it.

It has the 'side or additional benefit' being requested here. The ability
to filter the child table by some attribute. For example, if the child
tables are used for partitioning, and the attribute were to keep a date
range, the field restriction optimization could be used to automatically
determine the set of tables to use for the date range specified. With
such a change, it would even work automatically if the date ranges
overlapped for some reason. Selecting a table name by date is hacky. This
sort of solution would be a general solution to the problem.

If the original code suggested - the use of the table oid, and comparing
it to the rows table oid, is supported by PostgreSQL (I have never tried
it), it seems like a miss for the optimizer not to understand what it
means. Each row has one table that it is associated with. It's quite
obviously criteria that could be used to restrict the table search, and
belongs in any COMPLETE implementation of a restriction optimizer.

I still prefer Jim Nasby's model, though. I think it easily extends such
that the table row oid IS an automatic constant for all rows of a table.
It's a natural extension, and takes away the exceptional nature of the
table row oid.

Cheers,
mark

-- 
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/    |_     |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada
 One ring to rule them all, one ring to find them, one ring to bring them all                      and in the darkness
bindthem...
 
                          http://mark.mielke.cc/