Re: Postgres-7.0.2 optimization question

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.

ivr
--
"... car il faut convenir que Dieu n'a crée les femmes que pour
apprivoiser les hommes"
Voltaire