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(at)bigpanda(dot)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.
>