Postgres Pro
Downloads
Home   >   mailing lists

Thread: Help with query. (*)

Help with query. (*)

From
"Diehl, Jeffrey"
Date:
I'm having difficulty writing a query which I really can't live without...

I need to get a list of records from table A for which there are
corresponding records in table B.  I've tried to use the intersect clause,
but it doesn't seem to work, or it runs far too long.  For example:

select * from A
    where 1=1
    intersect select * from A where
    B.x=A.x
    and A.y=B.y
    and A.z=B.z
    limit 100

I need the most efficient method possible; my A tables have upward of 5
Million records.  The B table, btw, only has about 100 records.


Any help will be most appreciated.

Re: Help with query. (*)

From
Ken Corey
Date:
[NOTE: I'm a pgsql newbie myself.  Take this reply with a large-ish
grain of salt!)

Shouldn't it be something straightforward like:
select a.a, a.b, a.c, ...
  from a a,
       b b
 where a.x = b.x,
       and a.y = b.y, ...   (I'd watch out for too many clauses here...
                if you've got a lot of clauses, you're probably not normalized
                as much as you should be.)

If you have indexes on the relevant fields, you shouldn't get a table
scan and this should return rather quickly, right?

-Ken


"Diehl, Jeffrey" wrote:
>
> I'm having difficulty writing a query which I really can't live without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B.  I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long.  For example:
>
> select * from A
>         where 1=1
>         intersect select * from A where
>         B.x=A.x
>         and A.y=B.y
>         and A.z=B.z
>         limit 100
>
> I need the most efficient method possible; my A tables have upward of 5
> Million records.  The B table, btw, only has about 100 records.
>
> Any help will be most appreciated.

RE: Help with query. (*)

From
"Diehl, Jeffrey"
Date:
Well, actually, I want to eventually delete the records from A if there is
an entry in B.  That's why I am trying to use such a screwed up query. ;^)

Thanx,
Mike Diehl.

-----Original Message-----
From: Ken Corey
To: Diehl, Jeffrey
Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org
Sent: 1/17/01 12:34 PM
Subject: Re: Help with query.   (*)
Importance: High

[NOTE: I'm a pgsql newbie myself.  Take this reply with a large-ish
grain of salt!)

Shouldn't it be something straightforward like:
select a.a, a.b, a.c, ...
  from a a,
       b b
 where a.x = b.x,
       and a.y = b.y, ...   (I'd watch out for too many clauses here...
                if you've got a lot of clauses, you're
probably not normalized
                as much as you should be.)

If you have indexes on the relevant fields, you shouldn't get a table
scan and this should return rather quickly, right?

-Ken


"Diehl, Jeffrey" wrote:
>
> I'm having difficulty writing a query which I really can't live
without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B.  I've tried to use the intersect
clause,
> but it doesn't seem to work, or it runs far too long.  For example:
>
> select * from A
>         where 1=1
>         intersect select * from A where
>         B.x=A.x
>         and A.y=B.y
>         and A.z=B.z
>         limit 100
>
> I need the most efficient method possible; my A tables have upward of
5
> Million records.  The B table, btw, only has about 100 records.
>
> Any help will be most appreciated.

Re: RE: Help with query. (*)

From
Josh Berkus
Date:
Mike,
In that case, you want to use this construction:

DELETE FROM a 
WHERE EXISTS ( SELECT 1 FROM bWHERE b.1 = a.1  AND b.2 = a.2  AND b.3 = a.3 );

Of course, a good primary keying system would make this somewhat less
complex ...
                -Josh Berkus
-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small
businesses      fax  436-0137   and non-profit organizations.       pager 338-4078                               San
Francisco

Re: RE: Help with query. (*)

From
Tom Lane
Date:
"Diehl, Jeffrey" <jdiehl@sandia.gov> writes:
> Well, actually, I want to eventually delete the records from A if there is
> an entry in B.  That's why I am trying to use such a screwed up query. ;^)

If you don't mind being nonstandard, you could still do it in join
style:

DELETE FROM a WHERE a.x = b.x AND blah blah blah ...

Under Postgres this will form a join between A and B same as if you'd
said SELECT FROM a,b WHERE a.x = b.x etc, and then delete the rows of
A that are matched in the join.

If you want to be bog-SQL-standard then you have to use the WHERE EXISTS
construct that Josh mentioned.  Unfortunately, that's likely to be a
good deal slower (for large tables) under current releases of Postgres.
We have hopes of bringing the performance of the EXISTS variant up to
something close to the explicit join, but it's a version or two away
yet.

            regards, tom lane

Re: [GENERAL] Help with query. (*)

From
Patrick Welche
Date:
On Tue, Jan 16, 2001 at 01:42:45PM -0700, Diehl, Jeffrey wrote:
> I'm having difficulty writing a query which I really can't live without...
>
> I need to get a list of records from table A for which there are
> corresponding records in table B.  I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long.  For example:
>
> select * from A
>     where 1=1
>     intersect select * from A where
>     B.x=A.x
>     and A.y=B.y
>     and A.z=B.z
>     limit 100
>
> I need the most efficient method possible; my A tables have upward of 5
> Million records.  The B table, btw, only has about 100 records.

Guessing - my system isn't in a state to test just at the minute - is it

select A.* from A,B
 where A.x=B.x
   and A.y=B.y
   and A.z=B.z
 limit 100

that you want?