I've been coding for over 20 years now! (WOAH, do I feel old)
I've touched just about every resource imaginable under the Sun (too bad they were bought out by Oracle)
One point against that final query you show there. Using "OR" anywhere in a query generally destroys performance entirely, usually resulting in a full table scan. If you're dealing with a couple hundred or maybe a thousand rows, that's fine. In my line of work, a billion rows per table is quite common, and that would literally take hours to execute the query. Not entirely ideal on the DBA side, just to save some cleanliness on the developer's side.
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:
One point against that final query you show there. Using "OR" anywhere in a query generally destroys performance entirely, usually resulting in a full table scan. If you're dealing with a couple hundred or maybe a thousand rows, that's fine. In my line of work, a billion rows per table is quite common, and that would literally take hours to execute the query. Not entirely ideal on the DBA side, just to save some cleanliness on the developer's side.
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.
dateplananddateplan_allare used for the cases where date is filtered on or not, respectively, with no OR.dateplan_orincludes a where clause, whether we filter on date or not.The two plans that are actually used when filtering on date, using
dateplan_oranddateplan:And the two plans for
dateplan_oranddateplan_allwhen no date is given:If I create an index, it gets used by both queries when filtering by date: