Re: [SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field
Hi all,
I am now looking for a function to return the position of the first
position of the left most set bit. And optionally another to return the
position of the right most set bit.
I have been looking at
"http://graphics.stanford.edu/~seander/bithacks.html#OperationCounting";
but it seems it will take me a while to figure out bit manipulation.
Allan.
Allan Kamau wrote:
All was well with the code below, apologies to all who read my
previous email. The error (an oversight) was on my part. In the
"CREATE FUNCTION ..." statement I had FLOAT as the return type instead
of INTEGER.
Now the function runs smoothly. Preliminary results show it is orders
of magnitude faster than the LENGTH(REGEXP(CAST(myVarBit AS
TEXT),'0','','g')) solution.
Thanks again TJ and the rest of the team.
Allan
Allan Kamau wrote:
Thank you TJ and everyone else for the advise and the c code. Today I
did finally return to the 'number of bits set challenge' and managed
to compile and link the nbits c function which went smoothly. However
the function does crash my postgres server installation (8.3.3) with
a segmentation fault each time I call it for example SELECT
nbits_set(B'1101');
My C skills are very sparse and am unable to debug the function, I
have included the C code of this function. Is there something I may
have left out?
#include "postgres.h"
#include "utils/varbit.h"
#include "fmgr.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(nbits_set);
Datum
nbits_set(PG_FUNCTION_ARGS)
{
/* how many bits are set in a bitstring? */
VarBit *a = PG_GETARG_VARBIT_P(0);
int n=0;
int i;
unsigned char *ap = VARBITS(a);
unsigned char aval;
for (i=0; i < VARBITBYTES(a); ++i) {
aval = *ap; ++ap;
if (aval == 0) continue;
if (aval & 1) ++n;
if (aval & 2) ++n;
if (aval & 4) ++n;
if (aval & 8) ++n;
if (aval & 16) ++n;
if (aval & 32) ++n;
if (aval & 64) ++n;
if (aval & 128) ++n;
}
PG_RETURN_INT32(n);
}
Allan
Bruce Momjian wrote:
Jean-David Beyer wrote:
TJ O'Donnell wrote:
I use a c function, nbits_set that will do what you need.
I've posted the code in this email.
TJ O'Donnell
http://www.gnova.com
#include "postgres.h"
#include "utils/varbit.h"
Datum nbits_set(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(nbits_set);
Datum
nbits_set(PG_FUNCTION_ARGS)
{
/* how many bits are set in a bitstring? */
VarBit *a = PG_GETARG_VARBIT_P(0);
int n=0;
int i;
unsigned char *ap = VARBITS(a);
unsigned char aval;
for (i=0; i < VARBITBYTES(a); ++i) {
aval = *ap; ++ap;
if (aval == 0) continue;
if (aval & 1) ++n;
if (aval & 2) ++n;
if (aval & 4) ++n;
if (aval & 8) ++n;
if (aval & 16) ++n;
if (aval & 32) ++n;
if (aval & 64) ++n;
if (aval & 128) ++n;
}
PG_RETURN_INT32(n);
}
Hi all,
Am looking for a fast and efficient way to count the number of
bits set (to 1) in a VARBIT field. I am currently using
"LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS
TEXT),'0','','g'))".
Allan.
When I had to do that, in days with smaller amounts of RAM, but
very long
bit-vectors, I used a faster function sort-of like this:
static char table[256] = {
0,1,1,2,1,2,2,3,1,.
};
Then like above, but instead of the loop,
n+= table[aval];
You get the idea.
Uh, I was kind of confused by this, even when I saw a full
implementation:
http://graphics.stanford.edu/~seander/bithacks.html#CountBitsSetTable
Actually, this looks even better:
http://graphics.stanford.edu/~seander/bithacks.html#CountBitsSetKernighan
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Permanent alias for postgresql table
Hi list, I'm searching for a way to create permanent alias for tablenames in postgresql. We are storing various versions of a routable network in postgresql (postgis, pgrouting) and access a certain version with a bunch of php-skripts. We like to use aliases for the "currently used tables" oo be able to relink the current tables rapidly by changing the alias target. Any idea - or is this approach nonsense? Marco -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Permanent alias for postgresql table
Marco Lechner, 12.03.2009 13:59: Hi list, I'm searching for a way to create permanent alias for tablenames in postgresql. We are storing various versions of a routable network in postgresql (postgis, pgrouting) and access a certain version with a bunch of php-skripts. We like to use aliases for the "currently used tables" oo be able to relink the current tables rapidly by changing the alias target. Any idea - or is this approach nonsense? A view? CREATE VIEW constant_table_name AS SELECT * FROM current_table Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Permanent alias for postgresql table
Hi Mina, thanks for your answer. I thought about that, but don't views decrease performance, because they are "calculated" on access? Marco On Thu, 12 Mar 2009 13:34:39 + Mina R Waheeb wrote: > Use views, > > mytablev1 and we have a view mytable selecting * from > mytablev1 > and when we need to update the target we alter the view > to select from > mytablev2 > > On Thu, Mar 12, 2009 at 12:59 PM, Marco Lechner < > [email protected]> wrote: > > > Hi list, > > > > I'm searching for a way to create permanent alias for > > tablenames in postgresql. We are storing various > versions > > of a routable network in postgresql (postgis, > pgrouting) > > and access a certain version with a bunch of > php-skripts. > > We like to use aliases for the "currently used tables" > oo > > be able to relink the current tables rapidly by > changing > > the alias target. > > > > Any idea - or is this approach nonsense? > > > > Marco > > > > -- > > Sent via pgsql-sql mailing list > ([email protected]) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Permanent alias for postgresql table
On Thu, Mar 12, 2009 at 03:26:47PM +0100, Marco Lechner wrote: > Hi Mina, > > thanks for your answer. I thought about that, but don't > views decrease performance, because they are "calculated" > on access? The query gets rewritten a bit, but it's not a big deal. A more important concern might be that to make it so you can add / modify data in the table, you'll need to create rules to rewrite UPDATE and INSERT queries on that view to instead affect the underlying table. - Josh / eggyknap signature.asc Description: Digital signature
Re: [SQL] Permanent alias for postgresql table
Marco Lechner, 12.03.2009 15:26: Hi Mina, thanks for your answer. I thought about that, but don't views decrease performance, because they are "calculated" on access? I'm not sure what you mean with "calculated". A view is just a SQL query. There is no difference in executing the SQL query that's behind a view or the view itself. Except for the minimal time it takes to retrieve the view definition. But I would never sacrifice easy of development or usage for the microseconds of overhead the VIEW generates. And the "overhead" (if at all) will be neglectable compared to the time it takes to return the result. Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] select count of all overlapping geometries and return 0 if none.
Hello all, I have 2 tables locations and user_tracker: locations has 2 columns location_name location_geometry user_tracker has 3 columns user_name user_geometry user_timestamp locations table is coordinates and names of areas of interest. user_tracker basically is an archive of a user's movements as he pans his map. I have a need to assign a ranking of locations based on how many times users have intersected this location. The problem I am having is that my query only returns locations that have been intersected by a user. I need it to return ALL locations and a zero if this location has not been intersected. As an example: LOCATIONS 1: Talahassee, FL | talahassee's bounding box 2: Manhattan, NY | Manhattan's bounding box 3: Frankfurt, GE | Frankfurt's bounding box USER_TRACKER john doe | geometry that overlaps Frankfurt | today john doe | geometry that overlaps Frankfurt | today john doe | geometry that overlaps Frankfurt | today john doe | geometry that overlaps Frankfurt | yesterday john doe | geometry that overlaps Frankfurt | Monday john doe | geometry that overlaps Frankfurt | Sunday Mary Jane | geometry that overlaps Manhattan | today Rob Roy | geometry that overlaps Manhattan | today Rob Roy | geometry that overlaps Manhattan | today I want to return the following: locations | number_visits Frankfurt | 6 Manhattan | 3 Talahassee | 0 My query only returns: Frankfurt | 6 Manhattan | 3 Now I have really simplified this example for readability, my actual tables are more complex. How can I accomplish this? My query: SELECT count(user_name) as number_visits, location_name from locations, user_tracker WHERE user_geometry && location_geometry Thanks in advance
Re: [SQL] Re: Efficiently determining the number of bits set in the contents of, a VARBIT field
Seems I have a solution based on the code TJ had provided for counting
the bits sets, for those interested below are the two functions.
#include "postgres.h"
#include "utils/varbit.h"
#include "fmgr.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(last_bit_set);
Datum
last_bit_set(PG_FUNCTION_ARGS)
{
/* position of last set bit of a bitstring? */
int n=0;
VarBit *a = PG_GETARG_VARBIT_P(0);
int i;
unsigned char *ap = VARBITS(a);
unsigned char aval;
int b=0;
int byte_cnt=0;
int last_bit_set_position=0;
for(i=0;i0)
first_bit_set_position=(8*byte_cnt)+b;
else
first_bit_set_position=0;
PG_RETURN_INT32(first_bit_set_position);
}
Allan.
On Thu, Mar 12, 2009 at 2:53 PM, Allan Kamau wrote:
> Hi all,
> I am now looking for a function to return the position of the first position
> of the left most set bit. And optionally another to return the position of
> the right most set bit.
>
> I have been looking at
> "http://graphics.stanford.edu/~seander/bithacks.html#OperationCounting"; but
> it seems it will take me a while to figure out bit manipulation.
>
> Allan.
>
> Allan Kamau wrote:
>>
>> All was well with the code below, apologies to all who read my previous
>> email. The error (an oversight) was on my part. In the "CREATE FUNCTION ..."
>> statement I had FLOAT as the return type instead of INTEGER.
>> Now the function runs smoothly. Preliminary results show it is orders of
>> magnitude faster than the LENGTH(REGEXP(CAST(myVarBit AS TEXT),'0','','g'))
>> solution.
>> Thanks again TJ and the rest of the team.
>>
>> Allan
>>
>> Allan Kamau wrote:
>>>
>>> Thank you TJ and everyone else for the advise and the c code. Today I did
>>> finally return to the 'number of bits set challenge' and managed to compile
>>> and link the nbits c function which went smoothly. However the function does
>>> crash my postgres server installation (8.3.3) with a segmentation fault each
>>> time I call it for example SELECT nbits_set(B'1101');
>>> My C skills are very sparse and am unable to debug the function, I have
>>> included the C code of this function. Is there something I may have left
>>> out?
>>>
>>>
>>>
>>> #include "postgres.h"
>>> #include "utils/varbit.h"
>>> #include "fmgr.h"
>>> #ifdef PG_MODULE_MAGIC
>>> PG_MODULE_MAGIC;
>>> #endif
>>>
>>> PG_FUNCTION_INFO_V1(nbits_set);
>>> Datum
>>> nbits_set(PG_FUNCTION_ARGS)
>>> {
>>> /* how many bits are set in a bitstring? */
>>> VarBit *a = PG_GETARG_VARBIT_P(0);
>>> int n=0;
>>> int i;
>>> unsigned char *ap = VARBITS(a);
>>> unsigned char aval;
>>> for (i=0; i < VARBITBYTES(a); ++i) {
>>> aval = *ap; ++ap;
>>> if (aval == 0) continue;
>>> if (aval & 1) ++n;
>>> if (aval & 2) ++n;
>>> if (aval & 4) ++n;
>>> if (aval & 8) ++n;
>>> if (aval & 16) ++n;
>>> if (aval & 32) ++n;
>>> if (aval & 64) ++n;
>>> if (aval & 128) ++n;
>>> }
>>> PG_RETURN_INT32(n);
>>> }
>>>
>>> Allan
>>>
>>> Bruce Momjian wrote:
Jean-David Beyer wrote:
>
> TJ O'Donnell wrote:
>
>>
>> I use a c function, nbits_set that will do what you need.
>> I've posted the code in this email.
>>
>> TJ O'Donnell
>> http://www.gnova.com
>>
>> #include "postgres.h"
>> #include "utils/varbit.h"
>>
>> Datum nbits_set(PG_FUNCTION_ARGS);
>> PG_FUNCTION_INFO_V1(nbits_set);
>> Datum
>> nbits_set(PG_FUNCTION_ARGS)
>> {
>> /* how many bits are set in a bitstring? */
>>
>> VarBit *a = PG_GETARG_VARBIT_P(0);
>> int n=0;
>> int i;
>> unsigned char *ap = VARBITS(a);
>> unsigned char aval;
>> for (i=0; i < VARBITBYTES(a); ++i) {
>> aval = *ap; ++ap;
>> if (aval == 0) continue;
>> if (aval & 1) ++n;
>> if (aval & 2) ++n;
>> if (aval & 4) ++n;
>> if (aval & 8) ++n;
>> if (aval & 16) ++n;
>> if (aval & 32) ++n;
>> if (aval & 64) ++n;
>> if (aval & 128) ++n;
>> }
>> PG_RETURN_INT32(n);
>> }
>>
>>
>>
>>
>>>
>>> Hi all,
>>> Am looking for a fast and efficient way to count the number of bits
>>> set (to 1) in a VARBIT field. I am currently using
>>> "LENGTH(REGEXP_REPLACE(CAST(a.somefield_bit_code AS TEXT),'0','','g'))".
>>>
>>> Allan.
>>>
>>
>>
>
> When I had to do that, in days with smaller amounts of RAM, but very
> long
> bit-vectors, I used a faster function sort-of like this:
>
> static char table[256] = {
> 0,1,1,2,1,2,2,3,1,.
> };
>
> Then like above, but instead of the loop,
>
> n+= table[aval];
>
>>>
Re: [SQL] Permanent alias for postgresql table
-- On Thu, 3/12/09, Marco Lechner wrote: > From: Marco Lechner > Subject: Re: [SQL] Permanent alias for postgresql table > To: [email protected] > Date: Thursday, March 12, 2009, 2:26 PM > Hi Mina, > > thanks for your answer. I thought about that, but don't > views decrease performance, because they are > "calculated" > on access? > > Marco > > On Thu, 12 Mar 2009 13:34:39 + > Mina R Waheeb wrote: > > Use views, > > > > mytablev1 and we have a view mytable selecting * from > > mytablev1 > > and when we need to update the target we alter the > view > > to select from > > mytablev2 > > > > On Thu, Mar 12, 2009 at 12:59 PM, Marco Lechner < > > [email protected]> wrote: > > > > > Hi list, > > > > > > I'm searching for a way to create permanent > alias for > > > tablenames in postgresql. We are storing various > > versions > > > of a routable network in postgresql (postgis, > > pgrouting) > > > and access a certain version with a bunch of > > php-skripts. > > > We like to use aliases for the "currently > used tables" > > oo > > > be able to relink the current tables rapidly by > > changing > > > the alias target. > > > > > > Any idea - or is this approach nonsense? > > > > > > Marco > > > > > > -- > > > Sent via pgsql-sql mailing list > > ([email protected]) > > > To make changes to your subscription: > > > http://www.postgresql.org/mailpref/pgsql-sql > > > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql you can use partition table, whit a column whit the version of the data and create the partition for this column -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] subquery question
Hi,
I have a table: (date timestamp, id integer, value integer)
What Iam trying to do is to get a result that looks like this:
day sum_oddsum_even
2009-01-01 656578867
2009-01-02 876785 87667
basically a need to combine these two queries into one:
SELECTdate_trunc('day',date) AS day, sum(value) AS sum_odd
FROM xyz WHEREid % 2 = 1GROUP BY date_trunc('day',date)
SELECTdate_trunc('day',date) AS day, sum(value) AS sum_even
FROM xyz WHEREid % 2 = 0GROUP BY date_trunc('day',date)
I found various ways to do this via unions or joins, but none of them
seem efficient, what is the best way to do that ?
thank you very much
Sebastian
Re: [SQL] subquery question
Does this help
Here is my test table data.
ID;DATE;VALUE
1;"2009-03-13";5
2;"2009-03-13";2
3;"2009-03-11";1
4;"2009-03-11";2
5;"2009-03-11";3
SELECT mydate AS day,
SUM(CASE WHEN id % 2 = 1 THEN value END) AS sum_odd,
SUM(CASE WHEN id % 2 = 0 THEN value END) AS sum_even
FROM xyz
GROUP BY mydate;
DATE;SUM_ODD;SUM_EVEN
"2009-03-11";4;2
"2009-03-13";5;2
Check the plans generated to see if one query actually appears better
than another.
Bob
On Thu, Mar 12, 2009 at 9:06 PM, Sebastian Böhm wrote:
> Hi,
> I have a table: (date timestamp, id integer, value integer)
> What Iam trying to do is to get a result that looks like this:
> day sum_odd sum_even
> 2009-01-01 6565 78867
> 2009-01-02 876785 87667
>
> basically a need to combine these two queries into one:
> SELECT date_trunc('day',date) AS day, sum(value) AS sum_odd FROM
> xyz WHERE id % 2 = 1 GROUP BY date_trunc('day',date)
> SELECT date_trunc('day',date) AS day, sum(value) AS sum_even FROM
> xyz WHERE id % 2 = 0 GROUP BY date_trunc('day',date)
> I found various ways to do this via unions or joins, but none of them seem
> efficient, what is the best way to do that ?
>
> thank you very much
> Sebastian
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
