[SQL] Join question

[tyrrill_ed]

Hey All,

I have a query I'm trying to speed up, and I was hoping someone could
help me.  I have a three tables a and b hold data, and c just references
between a and b:

create table a (
   a_id int,
   x int
);

create table b (
   b_id int,
   x int
);

create table c (
   a_id int,
   b_id int
);

I am doing a query like this:

SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id =
c.b_id GROUP by a.x;

I only need to get one row from b for each row in a, and it really
doesn't matter which one.  I use max() to get a single value from table
b.  There are generally be dozens to hundreds of rows in b for each row
in a.  The problem is when I have a query with tens of thousands of rows
in a that the join with b will have millions of rows, and is really
slow.  The group by effectively reduces the results down to what I want,
but it still has to process the millions of rows.  Does anyone know a
way I could restructure this query to get only one b for each a in a
faster way?

Thanks,
Ed Tyrrill

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Bit string help, please

[tyrrill_ed]

Hey PostgreSQL Gurus,

I am experimenting with PostgreSQL bit strings to see if they might help
with some performance issues I am having.  I added a "bit varying"
column to one of my tables.  I have a PL/pgSQL function with an insert
statement into this table.  For the bit varying column I would like to
insert a bit string of length n with the highest order bit being 1 and
all the other bits being 0.  Given a table definition:

create table table1 (
   a int,
   b bit varying
);

I hoped I could use the feature where casting right pads 0s to do
something like this:

insert into table1 values( DEFAULT, B'1'::bit( n ) );

Where n is one of the parameters to the PL/pgSQL function, but that
doesn't work.  PostgreSQL doesn't like having a variable for the bit
string length.  Does anyone have any ideas how I could achieve this?

Thanks,
Ed Tyrrill

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql

Re: [SQL] Bit string help, please

[tyrrill_ed]

I tried this real quick at the psql command prompt, and unfortunately it
doesn't work:

mydb=# select ('1' || repeat('0',7))::bit varying;
ERROR:  cannot cast type text to bit varying

I appreciate the try though.  Any other ideas?  I am using PostgreSQL
8.2.3.1.  I don't know if that matters too much.

Thanks,
Ed
 

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 06, 2008 12:14 AM
To: Tyrrill, Ed
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Bit string help, please

[EMAIL PROTECTED] wrote:
> 
> insert into table1 values( DEFAULT, B'1'::bit( n ) );
> 
> Where n is one of the parameters to the PL/pgSQL function, but that
> doesn't work.  PostgreSQL doesn't like having a variable for the bit
> string length.  Does anyone have any ideas how I could achieve this?

Try casting from a string:
   SELECT ('1' || repeat('0', n-1))::bit varying;


-- 
   Richard Huxton
   Archonet Ltd


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql

Re: [SQL] Bit string help, please

[tyrrill_ed]

Thanks for the help again, Richard.  I will look into upgrading to 8.3
as I think that will be the best solution.

Ed Tyrrill

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 06, 2008 10:36 AM
To: Tyrrill, Ed
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Bit string help, please

[EMAIL PROTECTED] wrote:
> I tried this real quick at the psql command prompt, and unfortunately
it
> doesn't work:
> 
> mydb=# select ('1' || repeat('0',7))::bit varying;
> ERROR:  cannot cast type text to bit varying
> 
> I appreciate the try though.  Any other ideas?  I am using PostgreSQL
> 8.2.3.1.  I don't know if that matters too much.

Ah, sorry - that was an 8.3-only cast.

I think your only real option will be to build your query as a string 
and use EXECUTE to get the bit-varying you want in your function.

-- 
   Richard Huxton
   Archonet Ltd


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql