Re: improving speed of query that uses a multi-column "filter" ? - Mailing list pgsql-general

From Jonathan Vanasco
Subject Re: improving speed of query that uses a multi-column "filter" ?
Date
Msg-id E9FE22AA-A9F1-4BA5-907B-E20D70401291@2xlp.com
Whole thread Raw
In response to Re: improving speed of query that uses a multi-column "filter" ?  (John R Pierce <pierce@hogranch.com>)
List pgsql-general
On Sep 30, 2014, at 8:04 PM, John R Pierce <pierce@hogranch.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
allownull -- 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
thisas 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
thisinteger, such that one bit indicates the value was 'NULL', and the other bit is the true/false state if that first
bitisn'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/functionthat manages a filter_test column that is an int, give each filter a bit value, and then just run a
scanon that.  It wouldn't be much more work to test that and dedicated Bool columns for each filter. 









pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: improving speed of query that uses a multi-column "filter" ?
Next
From: Misa Simic
Date:
Subject: Re: improving speed of query that uses a multi-column "filter" ?