On Tuesday 04 October 2005 23:08, Michael Fuhr wrote:
> On Tue, Oct 04, 2005 at 09:32:41PM +0200, han.holl@informationslogik.nl wrote:
> > I've got a table with an index, let's call it fase.
> >
> > The following query is fine: 'select something from table where fase =
> > '1';
> >
> > However, this is disastrously slow:
> > select something from table where fase = '1' or fase = '2';
>
> Could we see some EXPLAIN ANALYZE output? What version of PostgreSQL
> are you using? Have you run VACUUM ANALYZE on the table to remove
> dead tuples and update the statistics? Have you considered clustering
> the table on fase's index?
>
Oh, well, thanks. I hadn't realized that a newly loaded database needs a vacuum analyze to begin with. And what's worse, I had the impression that vacuum full would include analyze, wich I see now it doesn't.
I'm not a database administrator, and I'm afraid it shows. I'm going to read a lot of docs in the coming months, because real people depend on reasonable performance of our databases.
Cheers, and thanks again,
Han Holl
PS We still have to be careful how to formulate conditions:
where fase in ('1','2')
is ok, and uses the index, but the logically identical:
where position(fase in '12') >= 1
does a sequential scan.