From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: improving speed of query that uses a multi-column "filter" ? |
Date: | 2014-10-01 00:04:43 |
Message-ID: | 542B451B.6040505@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9/30/2014 4:50 PM, Jonathan Vanasco wrote:
> WHERE (col_1 IS NULL ) AND (col_2 IS NULL) AND ((col_3 IS NULL) OR (col_3 = col_1))
if col_1 IS NULL, then that OR condition doesn't make much sense.
just saying...
these 4 columns are all nullable booleans, so they can be TRUE, FALSE,
or NULL ? with 4 columns, there's 3^4 = 81 possible combinations of
these values... you might get better speeds encoding this as a single
SHORT INTEGER, and enumerating those 81 states, then just do equals or
IN (set of values) conditions... of course, this might make a lot of
OTHER code more complicated. It might be easier to make each col_X 2
bits of this integer, such that one bit indicates the value was 'NULL',
and the other bit is the true/false state if that first bit isn't set,
this would make testing individual bits somewhat better.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Vanasco | 2014-10-01 00:46:12 | Re: improving speed of query that uses a multi-column "filter" ? |
Previous Message | Jonathan Vanasco | 2014-09-30 23:50:20 | improving speed of query that uses a multi-column "filter" ? |