Re: Postgres-7.0.2 optimization question

You might want to try:
 delete from foo where foo2=42::int8

IIRC, there is an issue with int8 indexes and integer constants
(which are treated as int4).

Stephan Szabo
sszabo@bigpanda.com

On Fri, 13 Oct 2000, Igor V. Rafienko wrote:

> on Oct 13, 2000, 07:55, Adam Ruth std::cout'ed:
>
> | Post the query you're using, there may be a way to rewrite it to use the
> | index.  I've found this to be true on all kinds of DBMSs.
>
>
> Okidoki (somewhat simplified (there are 5 other columns as well, but
> they have nothing to do with delete)):
>
>
> create table foo (
>      foo1  int8    not null,
>      foo2  int8    not null,
>      data1 varchar check( data_1 in ( 'zot', 'qux' ) ) not null,
>      data2 varchar null,
>
>      primary key (foo1,foo2),
>      unique (foo2,data1)
> );
>
> And then an index on foo2:
>
> create index foobar on foo( foo2 );
>
> And then a vacuumdb --analyze.
>
> The query cannot be made simpler, imvho, but since you've asked:
>
> delete from foo where foo2 = 42;
>
>
> There is also a foreign key into this table from a primary key of another
> table. Yes, there is an index on the other table as well.
>