On Mon, 7 Jul 2003, Matthew Nuzum wrote:
> > A common problem is a table like this:
> >
> > create table test (info text, id int8 primary key);
> > insert into test values ('ted',1);
> > .. a few thousand more inserts;
> > vacuum full;
> > analyze;
> > select * from test where id=1;
> >
> > will result in a seq scan, always, because the 1 by itself is
> > autoconverted to int4, which doesn't match int8 automatically. This
> > query:
> >
> > select * from test where id=1::int8
> >
> > will cast the 1 to an int8 so the index can be used.
> >
> >
>
> Hey Scott, this is a little scary because I probably have a lot of this
> going on...
>
> Is there a way to log something so that after a day or so I can go back and
> look for things like this that would be good candidates for optimization?
>
> I've got fast enough servers that currently the impact of this problem might
> not be too obvious, but I suspect that after the server gets loaded up the
> impact will become more of a problem.
>
> By the way, I must say that this thread has been very useful.
Well, you can turn on some of the newer logging features that tell you how
long the query took to run.
Look here:
http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-LOGGING
and here:
http://developer.postgresql.org/docs/postgres/runtime-config.html#RUNTIME-CONFIG-STATISTICS
Note that those are the developer docs. I'm pretty sure the first one has
a corrolary to the 7.3.x docs, but the second set (log_statement_stats,
parser_stats, etc...) looks new for 7.4