Re: How to optimize WHERE column_a IS NOT NULL OR column_b = 'value' - Mailing list pgsql-sql

Hi folks,

I have the following schema (simplified for this example).

create table folder(id integer primary key, name varchar not null);

create table document(id serial primary key, name varchar not null, owner_id integer not null, folder_id integer references folder(id));

create index document_owner_idx ON document(owner_id);

create index document_folder_idx ON document(folder_id);

insert into folder(id, name) values(1, 'Folder A');

insert into folder(id, name) values(2, 'Folder B');

insert into document(name, owner_id, folder_id) values('Document A',  1, 1);

insert into document(name, owner_id, folder_id) values('Document B',  1, NULL);

insert into document(name, owner_id, folder_id) values('Document C',  2, 2);

insert into document(name, owner_id, folder_id) values('Document D',  2, NULL);

select f.id, f.name, doc.id, doc.owner_id, doc.name

FROM document doc left outer join folder f ON doc.folder_id = f.id

WHERE doc.folder_id is not null OR doc.owner_id = 1;

Is the a way to write a query which uses an index efficiently for such a schema?

I'd like to eliminate the Filter: ((folder_id IS NOT NULL) OR (owner_id = 1)) and rather have "index cond" insted, is that possible?