Edit: I should have used an earlier date for my tests, and maybe a second clause. A quick test with a second where clause (not included below) seems to show exactly the same plan is used both with and without the OR parts.
The largest tables I deal with are less than 1 million rows, so my pressures are definitely different to yours.
I did some tests in postgesql, and I can't see any difference in the query plan that the database uses, whether I include OR's or not. I'm far from a database expert, so please let me know if I've misunderstood something.
There are two cases here: a date is given to be filtered on or isn't. There are two approaches for each of these cases -- using an OR, and not using one.
postgres=# CREATE TABLE town (
postgres(# id SERIAL UNIQUE NOT NULL,
postgres(# name TEXT NOT NULL,
postgres(# founded date NOT NULL
postgres(# );
CREATE TABLE
postgres=# insert into town (
postgres(# name, founded
postgres(# )
postgres-# select
postgres-# md5(random()::text),
postgres-# ('01-01-0001'::date + (random() * interval '2000 year'))::date
postgres-# from generate_series(1, 10000000) s(i)
postgres-# ;
INSERT 0 10000000
postgres=# PREPARE dateplan_or(date) AS
postgres-# SELECT * FROM town WHERE ($1 IS NULL OR founded >= $1);
PREPARE
postgres=#
postgres=# PREPARE dateplan(date) AS
postgres-# SELECT * FROM town WHERE founded >= $1;
PREPARE
postgres=#
postgres=# PREPARE dateplan_all(date) AS
postgres-# SELECT * FROM town;
PREPARE
dateplan and dateplan_all are used for the cases where date is filtered on or not, respectively, with no OR. dateplan_or includes a where clause, whether we filter on date or not.
The two plans that are actually used when filtering on date, using dateplan_or and dateplan:
Edit: I should have used an earlier date for my tests, and maybe a second clause. A quick test with a second where clause (not included below) seems to show exactly the same plan is used both with and without the OR parts.
The largest tables I deal with are less than 1 million rows, so my pressures are definitely different to yours.
I did some tests in postgesql, and I can't see any difference in the query plan that the database uses, whether I include OR's or not. I'm far from a database expert, so please let me know if I've misunderstood something.
There are two cases here: a date is given to be filtered on or isn't. There are two approaches for each of these cases -- using an OR, and not using one.
dateplan
anddateplan_all
are used for the cases where date is filtered on or not, respectively, with no OR.dateplan_or
includes a where clause, whether we filter on date or not.The two plans that are actually used when filtering on date, using
dateplan_or
anddateplan
:And the two plans for
dateplan_or
anddateplan_all
when no date is given:If I create an index, it gets used by both queries when filtering by date: