agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: How to optimize WHERE column_a IS NOT NULL OR column_b = 'value'
Date: Mon, 11 Aug 2014 18:01:17 +0200 (CEST)
Message-ID: <VisenaEmail.2b.ad71545f6913bea5.147c5b987cd@tc7-on> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
view thread (7+ messages) latest in thread
Message-ID: <VisenaEmail.2b.ad71545f6913bea5.147c5b987cd@tc7-on>
Permalink: ../VisenaEmail.2b.ad71545f6913bea5.147c5b987cd@tc7-on/
Also on: postgresql.org/message-id/VisenaEmail.2b.ad71545f6913bea5.147c5b987cd@tc7-on
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreas@visena.com
Subject: Re: How to optimize WHERE column_a IS NOT NULL OR column_b = 'value'
In-Reply-To: <VisenaEmail.2b.ad71545f6913bea5.147c5b987cd@tc7-on>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox