Re: indexes spanning multiple tables

On E, 2005-08-22 at 16:01 -0400, Tom Lane wrote:
> Joshua N Pritikin <jpritikin(at)pobox(dot)com> writes:
> > Is anybody working on allowing indexes to span multiple tables?
> > IF not, I'll give it a try.
>
> Wouldn't recommend it as a project for a beginning backend hacker;
> the locking considerations alone are a bit daunting.
>
> See the archives for prior discussions.

What could perhaps be within reach of a very determined and brilliant
beginning backend hacker :) would be an index_merge_scan or
dual_index_scan access method, which sits atop of two index scans and
fetches always the smallest one, thus enabling getting tuples in index
order from two UNION'ed tables with indexes on ordering column (or just
getting the top/bottom tuple for things like max()/min() optimisation;

I mean things like this

SELECT A FROM T1
UNION
SELECT A FROM T2
ORDER BY A
LIMIT 12;

or

CREATE TABLE TP (I SERIAL PRIMARY KEY);
CREATE TABLE TC1 () INHERITS (TP);
CREATE TABLE TC2 () INHERITS (TP);
CREATE TABLE TC3 () INHERITS (TP);
SELECT MAX(I) FROM TP;

The latter could be then made to produce the following plan

QUERY PLAN
-------------------------------------------------------------------------------
Limit
-> Index Merge Scan
-> Index Scan using tc1_pkey on tc1
-> Index Merge Scan
-> Index Scan using tc2_pkey on tc2
-> Index Scan using tc3_pkey on tc3

Together with Partition Elimination this could be used in data
warehousing for queries like 'ten most expensive sales during 1st
querter' so I CC: this to bizgres list too, which is incidentally
another list where multi-table indexes are sometimes discussed.

But I'm afraid that tweaking the planner and optimizer to use this new
access method will probably not be within powers of even a determined
and brilliant _beginning_ backend hacker :(

--
Hannu Krosing <hannu(at)skype(dot)net>