Re: Thanks, naming conventions, and count()

doesn't this defeat the reasons for going to numerics? is there a reason
why its such a difficult thing to do a SELECT oid on pg_database and
pg_class to get this information? that's what I've been doing when I need
to know *shrug*

On Sun, 29 Apr 2001, Bruce Momjian wrote:

> > First off I just wanted to give a big 'thank you' to all the
> > developers and contributors who have made PostgreSQL what it is
> > today. I haven't come across a single thing since my first
> > experience with it a few years ago that hasn't been corrected,
> > sped up, or otherwise postively enhanced!
> >
> > In working with 7.1 over the past couple weeks, I've noted the
> > following mods may add to usability and speed:
> >
> > o v7.1 changed the database naming convention to be all numeric;
> > I suggest having
> > the DB engine create symbolic links when creating a new DB
> > and subsequent tables. For instance, in creating a database
> > 'foo' with table 'bar' the /path/to/pgsql/data/base folder
> > will have a new folder named something like '18720'; this
> > folder could also have a symbolic link to 'foo'. Then in the
> > '18720' folder rather than just having numeric files for each
> > table, pk, index, etc. there could be symbolic links
> > following the naming convention 'bar', 'pk_foo_pkey',
> > 'field1_foo_ukey', 'field2_foo_key'.
> >
> > Maybe this would work best as configurable flag that could
> > be set during compilation or in the conf file.
>
> I think this is an excellent idea, and will add it to the TODO list. We
> agonized over moving to numeric names, and we couldn't think of a good
> way to allow administrators to know that table matched what files. The
> big problem is that there is no good way to make the symlinks reliable
> because in a crash, the symlink could point to a table creation that got
> rolled back or the renaming of a table that got rolled back. I think
> symlinks with some postmaster cleanup script that fixed bad symlinks
> would be great for 7,2.
>
> I have added this to the TODO list. If someone objects, I will remove
> it:
>
> * Add tablename symlinks for numeric file names
>
> >
> > o count() should use index scans for tables with a PK; scans
> > would be on the PK index;
> > even after running 'vacuum analyze' such a query still uses
> > a sequential scan. For instance, "select count(*) from bar"
> > and even "select(pk_name) from bar" both use sequential scans.
> > Likewise, scans on fields with indexes should use the index.
>
> The problem here is that now we don't have commit status in the index
> rows, so they have to check the heap for every row. One idea is to
> update the index status on an index scan, and if we can do that, we can
> easily use the index. However, the table scan is pretty quick.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org