Thread: indexes spanning multiple tables
Is anybody working on allowing indexes to span multiple tables? IF not, I'll give it a try. -- Make April 15 just another day, visit http://fairtax.org
Joshua N Pritikin <jpritikin@pobox.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.
regards, tom lane
while you weren't looking, Tom Lane wrote: [indexes spanning multiple tables] > Wouldn't recommend it as a project for a beginning backend hacker; > the locking considerations alone are a bit daunting. That being the case, is there a list anywhere of open/wish list/TODO items that are suitable for beginning pg hackers? I've been over the TODO list and found myself fairly daunted by what I see, but would still like to take a stab at contributing. /rls -- :wq
On E, 2005-08-22 at 16:01 -0400, Tom Lane wrote:
> Joshua N Pritikin <jpritikin@pobox.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 ->
IndexScan using tc1_pkey on tc1 -> Index Merge Scan -> Index Scan using tc2_pkey on tc2 -> Index Scan
usingtc3_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@skype.net>
Rosser Schwarz wrote: >while you weren't looking, Tom Lane wrote: > >[indexes spanning multiple tables] > > > >>Wouldn't recommend it as a project for a beginning backend hacker; >>the locking considerations alone are a bit daunting. >> >> > >That being the case, is there a list anywhere of open/wish list/TODO >items that are suitable for beginning pg hackers? I've been over the >TODO list and found myself fairly daunted by what I see, but would >still like to take a stab at contributing. > > A couple of nice visible projects on the TODO list that might be suitable for beginners: . Add "include file" functionality in postgresql.conf . Remove Money type, add money formatting for decimal type But actually, the best place to start is possibly doing cleanups. For example, gcc version 4 generates LOTS of compiler warnings. They need clearing up. Doing that might lead to yuo look at quite a lot of interesting code, which in turn might lead to more projects. Plus there are always tests and docs to write ;-) cheers andrew
Alvaro Herrera wrote: >On Mon, Aug 22, 2005 at 05:31:04PM -0400, Andrew Dunstan wrote: > > > >>A couple of nice visible projects on the TODO list that might be >>suitable for beginners: >> >>. Add "include file" functionality in postgresql.conf >>. Remove Money type, add money formatting for decimal type >> >> > >Actually they are both bad projects. The "include file" patch was >submitted by the @mohawksoft guy whose name I can't remember; it was >rejected with good reasons. The money type was proposed for removal >some time ago, and the author also asked not to. > > If the idea is bad it should be knocked off the list. If the implementation was bad it might well be a reason someone else should have a go. Last I recall on money, the only issue preventing its removal was that numeric formatting wasn't good enough. cheers andrew
On Mon, Aug 22, 2005 at 05:31:04PM -0400, Andrew Dunstan wrote: > > > Rosser Schwarz wrote: > > >while you weren't looking, Tom Lane wrote: > > > >[indexes spanning multiple tables] > > > > > > > >>Wouldn't recommend it as a project for a beginning backend hacker; > >>the locking considerations alone are a bit daunting. > >> > >> > > > >That being the case, is there a list anywhere of open/wish list/TODO > >items that are suitable for beginning pg hackers? I've been over the > >TODO list and found myself fairly daunted by what I see, but would > >still like to take a stab at contributing. > > > > > > > A couple of nice visible projects on the TODO list that might be > suitable for beginners: > > . Add "include file" functionality in postgresql.conf > . Remove Money type, add money formatting for decimal type > > But actually, the best place to start is possibly doing cleanups. > > For example, gcc version 4 generates LOTS of compiler warnings. They > need clearing up. Doing that might lead to yuo look at quite a lot of > interesting code, which in turn might lead to more projects. > > Plus there are always tests and docs to write ;-) Can someone turn these items into a "beginning hacker's TODO" as has been discussed before? Or find a way to mark them on the main TODO? If someone wants to tell me how this should be done and give me whatever files need to be changed I'd be happy to submit a patch. BTW, is the HTML for the website not maintained in CVS somewhere? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
On Mon, Aug 22, 2005 at 05:31:04PM -0400, Andrew Dunstan wrote: > A couple of nice visible projects on the TODO list that might be > suitable for beginners: > > . Add "include file" functionality in postgresql.conf > . Remove Money type, add money formatting for decimal type Actually they are both bad projects. The "include file" patch was submitted by the @mohawksoft guy whose name I can't remember; it was rejected with good reasons. The money type was proposed for removal some time ago, and the author also asked not to. > But actually, the best place to start is possibly doing cleanups. > > For example, gcc version 4 generates LOTS of compiler warnings. They > need clearing up. Yeah, please do. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "Es filósofo el que disfruta con los enigmas" (G. Coli)
If someone wants to mark easy items on the TODO list with some mark, like %, I can apply the patch. Please patch TODO and not TODO.html. --------------------------------------------------------------------------- Andrew Dunstan wrote: > > > Rosser Schwarz wrote: > > >while you weren't looking, Tom Lane wrote: > > > >[indexes spanning multiple tables] > > > > > > > >>Wouldn't recommend it as a project for a beginning backend hacker; > >>the locking considerations alone are a bit daunting. > >> > >> > > > >That being the case, is there a list anywhere of open/wish list/TODO > >items that are suitable for beginning pg hackers? I've been over the > >TODO list and found myself fairly daunted by what I see, but would > >still like to take a stab at contributing. > > > > > > > A couple of nice visible projects on the TODO list that might be > suitable for beginners: > > . Add "include file" functionality in postgresql.conf > . Remove Money type, add money formatting for decimal type > > But actually, the best place to start is possibly doing cleanups. > > For example, gcc version 4 generates LOTS of compiler warnings. They > need clearing up. Doing that might lead to yuo look at quite a lot of > interesting code, which in turn might lead to more projects. > > Plus there are always tests and docs to write ;-) > > cheers > > andrew > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
>> Actually they are both bad projects. The "include file" patch was >> submitted by the @mohawksoft guy whose name I can't remember; it was >> rejected with good reasons. The money type was proposed for removal >> some time ago, and the author also asked not to. Well the money type seems it should be a domain around numeric but other than that... > Last I recall on money, the only issue preventing its removal was that > numeric formatting wasn't good enough. As I continue to read the email .... ;) Is it good enough now? Sincerely, Joshua D. Drake > > cheers > > andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
Hello, Would work on one of the pl languages constitute a good place for a beginning hacker to start? plPerl, plPython, plRuby, and plPHP all need support for IN/OUT parameters I believe. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
On Mon, 2005-08-22 at 15:24 -0500, Rosser Schwarz wrote:
> while you weren't looking, Tom Lane wrote:
>
> [indexes spanning multiple tables]
>
> > Wouldn't recommend it as a project for a beginning backend hacker;
> > the locking considerations alone are a bit daunting.
>
> That being the case, is there a list anywhere of open/wish list/TODO
> items that are suitable for beginning pg hackers? I've been over the
> TODO list and found myself fairly daunted by what I see, but would
> still like to take a stab at contributing.
Utility commands (CREATE, ALTER, DROP) seem to be the easiest to deal
with since they are pretty much self contained:
* Allow TRUNCATE ... CASCADE/RESTRICT * Add a separate TRUNCATE permission * Add COMMENT ON for all
clusterglobal objects (roles, databases and tablespaces) * Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
* Have ALTER TABLE RENAME rename SERIAL sequence names
Another source of items on the TODO list is the Unsupported Features
portion of the SQL Conformance documentation:
http://www.postgresql.org/docs/8.0/interactive/unsupported-features-sql-standard.html
Identity and Generator support or possibly simple Assertions shouldn't
have too significant of a learning curve to implement. The amount of
work may be large but you don't need to dig into the difficult to do
right bits of code like the optimizer.
Joshua D. Drake wrote: > Hello, > > Would work on one of the pl languages constitute a good place for a > beginning hacker to start? > > plPerl, plPython, plRuby, and plPHP all need support for IN/OUT > parameters I believe. Probably need named parameter support first, I suspect. But I also suspect it's not beginner material - certainly the perl XS API is daunting dunno about the others. cheers andrew
Joshua D. Drake wrote: > > >> Actually they are both bad projects. The "include file" patch was > >> submitted by the @mohawksoft guy whose name I can't remember; it was > >> rejected with good reasons. The money type was proposed for removal > >> some time ago, and the author also asked not to. > > Well the money type seems it should be a domain around numeric but other > than that... I don't think you can do special output with DOMAIN. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Mon, Aug 22, 2005 at 05:53:14PM -0400, Bruce Momjian wrote: > > If someone wants to mark easy items on the TODO list with some mark, > like %, I can apply the patch. Please patch TODO and not TODO.html. I'll take a stab at this unless someone else beats me to it; though I'm not a coder myself so there will probably be some clean-up required. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
Jim C. Nasby wrote: > Can someone turn these items into a "beginning hacker's TODO" as has > been discussed before? Or find a way to mark them on the main TODO? > > If someone wants to tell me how this should be done and give me whatever > files need to be changed I'd be happy to submit a patch. Sure, submit a diff against doc/TODO and mark them with something like %. > BTW, is the HTML for the website not maintained in CVS somewhere? TODO.html is generated from doc/TODO automatically by me. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> * Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME That one is easy and handy. Chris
On Mon, Aug 22, 2005 at 04:01:04PM -0400, Tom Lane wrote: > Joshua N Pritikin <jpritikin@pobox.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. OK but since I'm not convinced that I can't succeed, I'm going to continue working on a patch. -- Make April 15 just another day, visit http://fairtax.org
On Mon, Aug 22, 2005 at 06:48:52PM -0400, Rod Taylor wrote: > Another source of items on the TODO list is the Unsupported Features > portion of the SQL Conformance documentation: > > http://www.postgresql.org/docs/8.0/interactive/unsupported-features-sql-standard.html Maybe we should just have a generic link from TODO to that info? It doesn't seem to make sense to keep the same info in two places... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
On Mon, Aug 22, 2005 at 07:09:10PM -0400, Bruce Momjian wrote: > Jim C. Nasby wrote: > > Can someone turn these items into a "beginning hacker's TODO" as has > > been discussed before? Or find a way to mark them on the main TODO? > > > > If someone wants to tell me how this should be done and give me whatever > > files need to be changed I'd be happy to submit a patch. > > Sure, submit a diff against doc/TODO and mark them with something like > %. Here's my stab at marking items. I picked items that I thought would either be well-contained or that would be pretty straightforward. But since I'm not very familiar with the code itself a lot of these could be way off-base. I tried to err on the side of marking things that might be boarderline since presumably it's easier for someone to see a marked item and veto it rather than look at the entire list and try and find new items. In any case, it wouldn't hurt for someone to make another pass after this is applied and look for easy items that I missed. BTW, while I was doing this it struck me that it might make sense to have a difficulty ranking of, say 1-5, instead of just marking beginner items. Thoughts? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461
Attachment
IMHO (as a wanbe pgsql hacker) it is more important to mark tasks as suitable for beginners, if they do not require in depth knowledge of the pgsql codebase, and not according to how easy they are in other terms. for example If a task requires a significant amount of new non trivial code which has little to do with existing code and is just plugged in one little place, I would consider this task suitable for beginners, as I do not assume beginner pgsql hackers are incompetent or even inexperienced programmers they are simply to pgsql. Meir On 8/24/05, Jim C. Nasby <jnasby@pervasive.com> wrote: > On Mon, Aug 22, 2005 at 07:09:10PM -0400, Bruce Momjian wrote: > > Jim C. Nasby wrote: > > > Can someone turn these items into a "beginning hacker's TODO" as has > > > been discussed before? Or find a way to mark them on the main TODO? > > > > > > If someone wants to tell me how this should be done and give me whatever > > > files need to be changed I'd be happy to submit a patch. > > > > Sure, submit a diff against doc/TODO and mark them with something like > > %. > > Here's my stab at marking items. I picked items that I thought would > either be well-contained or that would be pretty straightforward. But > since I'm not very familiar with the code itself a lot of these could be > way off-base. I tried to err on the side of marking things that might be > boarderline since presumably it's easier for someone to see a marked > item and veto it rather than look at the entire list and try and find > new items. In any case, it wouldn't hurt for someone to make another > pass after this is applied and look for easy items that I missed. > > BTW, while I was doing this it struck me that it might make sense to > have a difficulty ranking of, say 1-5, instead of just marking beginner > items. Thoughts? > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com 512-569-9461 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > > > >
Thanks, added. I think numbering them is too complicated. --------------------------------------------------------------------------- Jim C. Nasby wrote: > On Mon, Aug 22, 2005 at 07:09:10PM -0400, Bruce Momjian wrote: > > Jim C. Nasby wrote: > > > Can someone turn these items into a "beginning hacker's TODO" as has > > > been discussed before? Or find a way to mark them on the main TODO? > > > > > > If someone wants to tell me how this should be done and give me whatever > > > files need to be changed I'd be happy to submit a patch. > > > > Sure, submit a diff against doc/TODO and mark them with something like > > %. > > Here's my stab at marking items. I picked items that I thought would > either be well-contained or that would be pretty straightforward. But > since I'm not very familiar with the code itself a lot of these could be > way off-base. I tried to err on the side of marking things that might be > boarderline since presumably it's easier for someone to see a marked > item and veto it rather than look at the entire list and try and find > new items. In any case, it wouldn't hurt for someone to make another > pass after this is applied and look for easy items that I missed. > > BTW, while I was doing this it struck me that it might make sense to > have a difficulty ranking of, say 1-5, instead of just marking beginner > items. Thoughts? > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com 512-569-9461 [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073