Skip site navigation (1) Skip section navigation (2)

Re: Planner question - "bit" data types

From: Karl Denninger <karl(at)denninger(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Merlin Moncure <mmoncure(at)gmail(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner question - "bit" data types
Date: 2009-09-05 23:39:14
Message-ID: 4AA2F6A2.3090306@denninger.net (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Karl Denninger <karl(at)denninger(dot)net> writes:
>   
>> Tom Lane wrote:
>>     
>>> In that case you'd be wasting your time to get it to use an index
>>> for the condition anyway.  Maybe you need to take a step back and
>>> look at the query as a whole rather than focus on this particular
>>> condition.
>>>       
>> The query, sans this condition, is extremely fast and contains a LOT of
>> other conditions (none of which cause trouble.)
>> It is only attempting to filter the returned tuples on the permission
>> bit(s) involved that cause trouble.
>>     
>
> My comment stands: asking about how to use an index for this is the
> wrong question.
>
> You never showed us any EXPLAIN results,
Yes I did.  Go back and look at the archives.  I provided full EXPLAIN
and EXPLAIN ANALYZE results for the original query.  Sheesh.
> Or plan B, which I'd recommend, is to forget the mask business and go
> over to a boolean column per permission flag.  Then the planner would
> actually have decent statistics about the flag selectivities, and the
> queries would be a lot more readable too.  Your objection that you'd
> need an index per flag column is misguided --- at these selectivities
> an index is really pointless.  And I entirely fail to understand the
> complaint about it being unportable; you think "&" is more portable than
> boolean?  Only one of those things is in the SQL standard.
>
> 			regards, tom lane
>   
The point isn't portability to other SQL engines - it is to other
people's installations.  The bitmask is (since it requires only changing
the mask constants in the container file that makes the SQL calls by
reference) where explicit columns is not by a long shot.

In any event it looks like that's the only reasonable way to do this, so
thanks (I think)

-- Karl

Attachment: karl.vcf
Description: text/x-vcard (124 bytes)

In response to

Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2009-09-06 00:15:00
Subject: Re: Planner question - "bit" data types
Previous:From: Tom LaneDate: 2009-09-05 23:24:38
Subject: Re: Planner question - "bit" data types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group