From: | Jonathan Vanasco <postgres(at)2xlp(dot)com> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: improving speed of query that uses a multi-column "filter" ? |
Date: | 2014-10-01 00:46:12 |
Message-ID: | E9FE22AA-A9F1-4BA5-907B-E20D70401291@2xlp.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sep 30, 2014, at 8:04 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> if col_1 IS NULL, then that OR condition doesn't make much sense. just saying...
I was just making a quick example. There are two commonly used "filter sets", each are mostly on Bool columns that allow null -- but one checks to see if the row references itself in a particular column.
> these 4 columns are all nullable booleans, so they can be TRUE, FALSE, or NULL ?
Most of them, yes.
> 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.
That's interesting. I never thought of how Postgres processes the data.
For legacy reasons, I can't change the data types -- but I can add additional columns. So I could do a trigger/function that manages a filter_test column that is an int, give each filter a bit value, and then just run a scan on that. It wouldn't be much more work to test that and dedicated Bool columns for each filter.
From | Date | Subject | |
---|---|---|---|
Next Message | Misa Simic | 2014-10-01 04:34:58 | Re: improving speed of query that uses a multi-column "filter" ? |
Previous Message | John R Pierce | 2014-10-01 00:04:43 | Re: improving speed of query that uses a multi-column "filter" ? |