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