Re: PostgreSQL performance in simple queries

Tom Lane wrote:
> Joseph Shraibman <jks(at)selectacast(dot)net> writes:
> > Neil Conway wrote:
> >> PostgreSQL (< 7.5) won't consider using an indexscan when the predicate
> >> involves an integer literal and the column datatype is int2 or int8.
>
> > Is this fixed for 7.5? It isn't checked off on the TODO list at
> > http://developer.postgresql.org/todo.php
>
> It is. I don't know why Bruce hasn't checked it off.
>

OK, marked as done:

* -Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8,
float4, numeric/decimal too
>
> Some other stuff that needs work in TODO:
>
> : Bracketed items "[]" have more detailed.
>
> More detailed what? Grammar please.

Fixed. "more detail".

> : * Remove unreferenced table files and temp tables during database vacuum
> : or postmaster startup (Bruce)
>
> I'm not sure this is still needed given that we now log file deletion in
> WAL.

OK, removed.

>
> : * Allow pg_dump to dump sequences using NO_MAXVALUE and NO_MINVALUE
>
> Seems to be done.

OK.

>
> : * Prevent whole-row references from leaking memory, e.g. SELECT COUNT(tab.*)
>
> Done.

OK.

>
> : * Make LENGTH() of CHAR() not count trailing spaces
>
> Done.

OK.

>
> : * Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8,
> : float4, numeric/decimal too
>
> Done, per above.

Got it.

>
> : * Allow more ISOLATION LEVELS to be accepted, but issue a warning for them
>
> Presently we accept all four with no warning ...

OK. Warning part removed.

>
> : * Add GUC setting to make created tables default to WITHOUT OIDS
>
> Seems to be done, other than the argument about how pg_dump should work.

I did the pg_dump part using SET only where needed. That is done.

>
> : * Allow fastpast to pass values in portable format
>
> This was done in 7.4.

Removed.

>
> : * Move psql backslash database information into the backend, use nmumonic
> : commands? [psql]
>
> Spelling problem...

Fixed.

>
> : * JDBC
>
> With JDBC out of the core, I'm not sure why we still have a JDBC section
> in the core TODO.

Removed. If they want it they can get it from our CVS history.

> : * Have pg_dump -c clear the database using dependency information
>
> I think this works now. Not really tested, but in principle it should
> work.

OK.
>
> : * Cache last known per-tuple offsets to speed long tuple access
>
> This sounds exactly like attcacheoff, which has been there since
> Berkeley. Either remove this or fix the description to give some
> idea what's really meant.

Added "adjusting for NULLs and TOAST values. The issue is that when
NULLs or TOAST is present, those aren't useful. I was thinking we could
remember the pattern of the previous row and use those offsets if the
TOAST/NULL pattern was the same, or something like that. Is that a
valid idea?

> : * Automatically place fixed-width, NOT NULL columns first in a table
>
> This is not ever going to happen, given that we've rejected the idea of
> having separate logical and physical column positions.

Removed.

>
> : * Change representation of whole-tuple parameters to functions
>
> Done. (However, you might want to add something about supporting
> composite types as table columns, which isn't done.)

OK, marked a done, and added new line:

* Support composite types as table columns

> : * Allow the regression tests to start postmaster with -i so the tests
> : can be run on systems that don't support unix-domain sockets
>
> Done long ago.

Removed.

Thanks for the updates!

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073