Re: Thanks, naming conventions, and count() - Mailing list pgsql-hackers

> 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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026