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;

 

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.15..13.77 rows=4 width=76) (actual time=0.031..0.045 rows=3 loops=1)
   ->  Seq Scan on document doc  (cost=0.00..1.05 rows=4 width=44) (actual time=0.012..0.018 rows=3 loops=1)
         Filter: ((folder_id IS NOT NULL) OR (owner_id = 1))
         Rows Removed by Filter: 1
   ->  Index Scan using folder_pkey on folder f  (cost=0.15..3.17 rows=1 width=36) (actual time=0.005..0.006 rows=1 loops=3)
         Index Cond: (doc.folder_id = id)
 Planning time: 0.267 ms
 Execution time: 0.094 ms
(8 rows)

 

 

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?

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com