> On 22 Feb 2016, at 16:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> (BTW, is that index really on just a boolean column? It seems
> unlikely that "phoneable" would be a sufficiently selective
> condition to justify having an index on it. I'd seriously consider
> dropping that index as another solution approach.)
On that train of thought, I would think that a person or company would only be phoneable if they have a phone number
registeredsomewhere. That somewhere probably being in another table that's too far away from the current table to check
itstraight away - so this is an optimisation, right?
Where I see that going is as follows: A "contact" either has a phone number - in which case you'd probably rather get
thatphone number - or they don't, in which case a null value is often sufficient[1].
While a phone number certainly takes up more storage than a boolean, it wouldn't require an index (because it's
availableright there) nor the extra joins to look up the actual phone number. And if you'd still want to put an index
onit, the null values won't be indexed, which takes a bit off the burden of the larger field size.
You _could_ also take a shortcut and use a variation of your current approach by storing null instead of false for
phoneable,but then your index would contain nothing but true values which rather defeats the point of having an index.
Query-wise, I suspect that the number of "contacts" that have a phone number far outweighs the number that doesn't, in
whichcase it's more efficient to query for those that don't have one (fewer index hits) and eliminate those from the
resultsthan the other way around. In my experience, both the NOT EXISTS and the LEFT JOIN + WHERE phoneable IS NULL
tendto perform better.
A final variation on the above would be to have a conditional index on your PK for those "contacts" that are NOT
phoneable.That's probably the shortest and quickest list to query. I'd still prefer that field to contain something a
bitmore meaningful though...
Well, enough of my rambling!
Ad 1. It is possible that you cater for the possibility that you don't know whether a "contact" has a phone number or
not,in which case null would probably be the wrong choice for "no phone number" because then you wouldn't be able to
distinguishbetween "no phone number" and "I don't know".
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.