Thread: Is there any Faster workaround ??

Is there any Faster workaround ??

From

Rajesh Kumar Mallah

Date:

09 April 2002, 18:59:53

Hi folks,

We require to do below or similar quite often

DELETE  from t_a where email in (select email from t_b);

everyone knows "IN" is pain fully slow currently in pgsql
is the any fster alternative to above query

regds
Mallah.

Re: Is there any Faster workaround ??

From

David Stanaway

Date:

09 April 2002, 19:20:03

On Tue, 2002-04-09 at 14:54, Rajesh Kumar Mallah wrote:
> Hi folks,
>
> We require to do below or similar quite often
>
> DELETE  from t_a where email in (select email from t_b);
>
> everyone knows "IN" is pain fully slow currently in pgsql
> is the any fster alternative to above query
>

DELETE  from t_a where exists (select email from t_b where t_b.email =
t_a.email);


--
David Stanaway

Re: Is there any Faster workaround ??

From

Jean-Luc Lachance

Date:

09 April 2002, 20:02:03

How about:

DELETE  from t_a where exist (select email from t_b where t_b.email =
t_a.email)

Rajesh Kumar Mallah wrote:
> 
> Hi folks,
> 
> We require to do below or similar quite often
> 
> DELETE  from t_a where email in (select email from t_b);
> 
> everyone knows "IN" is pain fully slow currently in pgsql
> is the any fster alternative to above query
> 
> regds
> Mallah.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Thank you ! Re: Is there any Faster workaround ??

From

Rajesh Kumar Mallah

Date:

09 April 2002, 20:29:30

Thanks so much!

i think this ques. has been answered before also in
this mailing list  sorry for asking...

regds
mallah.

Jean-Luc Lachance wrote:

> How about:
>
> DELETE  from t_a where exist (select email from t_b where t_b.email =
> t_a.email)
>
> Rajesh Kumar Mallah wrote:
> >
> > Hi folks,
> >
> > We require to do below or similar quite often
> >
> > DELETE  from t_a where email in (select email from t_b);
> >
> > everyone knows "IN" is pain fully slow currently in pgsql
> > is the any fster alternative to above query
> >
> > regds
> > Mallah.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

new problem! Is there any Faster workaround ??

From

Rajesh Kumar Mallah

Date:

09 April 2002, 20:43:38

Hi folks, <p>I think there is still  a problem , <p>email_bank contains  ~ 1  million  records  and t_a contains 58
records<br />both are indexed on email_id ( integer colmn.)  i want to remove these 58 records <br />from email_bank 
whereemail_id  in t_a  &  email_bank match. <p>but the below is still slow ! <br /><font size="-1"></font> <font
size="-1"></font><p><fontsize="-1">explain delete from email_bank where exists (select email_id from t_a where
email_bank.email_id=t_a.email_id);</font><br /><font size="-1">NOTICE:  QUERY PLAN:</font><font
size="-1"></font><p><fontsize="-1">Seq Scan on email_bank  (cost=0.00..1760313.80 rows=1009397 width=6)</font><br
/><fontsize="-1">  SubPlan</font><br /><font size="-1">    ->  Seq Scan on t_a  (cost=0.00..1.73 rows=1
width=4)</font><fontsize="-1"></font><p><font size="-1">EXPLAIN</font><font size="-1"></font><p>PS: <br />I ran  VACUUM
ANALYZEon email_bank and t_a and  both are indexed on email_id  before <br />the explain . <br />  <p>regds <br
/>mallah.<br /><font size="-1"></font> <tt></tt><p>Rajesh Kumar Mallah wrote: <blockquote type="CITE">Hi folks, <p>We
requireto do below or similar quite often <p>DELETE  from t_a where email in (select email from t_b); <p>everyone knows
"IN"is pain fully slow currently in pgsql <br />is the any fster alternative to above query <p>regds <br />Mallah.
<p>---------------------------(endof broadcast)--------------------------- <br />TIP 1: subscribe and unsubscribe
commandsgo to majordomo@postgresql.org</blockquote>

Re: new problem! Is there any Faster workaround ??

From

Jeff Eckermann

Date:

10 April 2002, 14:45:28

A join probably will work best for you.  A quick way
to do an implicit join:

delete from email_bank where email_id = t_a.email_id;

--- Rajesh Kumar Mallah <mallah@trade-india.com>
wrote:
> Hi folks,
> 
> I think there is still  a problem ,
> 
> email_bank contains  ~ 1  million  records  and t_a
> contains 58 records
> both are indexed on email_id ( integer colmn.)  i
> want to remove these 58
> records
> from email_bank  where email_id  in t_a  & 
> email_bank match.
> 
> but the below is still slow !
> 
> 
> explain delete from email_bank where exists (select
> email_id from t_a where
> email_bank.email_id=t_a.email_id) ;
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on email_bank  (cost=0.00..1760313.80
> rows=1009397 width=6)
>   SubPlan
>     ->  Seq Scan on t_a  (cost=0.00..1.73 rows=1
> width=4)
> 
> EXPLAIN
> 
> PS:
> I ran  VACUUM ANALYZE on email_bank and t_a and 
> both are indexed on
> email_id  before
> the explain .
> 
> 
> regds
> mallah.
> 
> 
> Rajesh Kumar Mallah wrote:
> 
> > Hi folks,
> >
> > We require to do below or similar quite often
> >
> > DELETE  from t_a where email in (select email from
> t_b);
> >
> > everyone knows "IN" is pain fully slow currently
> in pgsql
> > is the any fster alternative to above query
> >
> > regds
> > Mallah.
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> 


__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/

Re: new problem! Is there any Faster workaround ??

From

Rajesh Kumar Mallah

Date:

11 April 2002, 05:43:34

Thanks Jeff .

i was not aware of this syntax , i will try it
next time  when i require to do something like this.

unfortunately i have deleted this time
using perl dbi


mallah.

Jeff Eckermann wrote:

> A join probably will work best for you.  A quick way
> to do an implicit join:
>
> delete from email_bank where email_id = t_a.email_id;
>
> --- Rajesh Kumar Mallah <mallah@trade-india.com>
> wrote:
> > Hi folks,
> >
> > I think there is still  a problem ,
> >
> > email_bank contains  ~ 1  million  records  and t_a
> > contains 58 records
> > both are indexed on email_id ( integer colmn.)  i
> > want to remove these 58
> > records
> > from email_bank  where email_id  in t_a  &
> > email_bank match.
> >
> > but the below is still slow !
> >
> >
> > explain delete from email_bank where exists (select
> > email_id from t_a where
> > email_bank.email_id=t_a.email_id) ;
> > NOTICE:  QUERY PLAN:
> >
> > Seq Scan on email_bank  (cost=0.00..1760313.80
> > rows=1009397 width=6)
> >   SubPlan
> >     ->  Seq Scan on t_a  (cost=0.00..1.73 rows=1
> > width=4)
> >
> > EXPLAIN
> >
> > PS:
> > I ran  VACUUM ANALYZE on email_bank and t_a and
> > both are indexed on
> > email_id  before
> > the explain .
> >
> >
> > regds
> > mallah.
> >
> >
> > Rajesh Kumar Mallah wrote:
> >
> > > Hi folks,
> > >
> > > We require to do below or similar quite often
> > >
> > > DELETE  from t_a where email in (select email from
> > t_b);
> > >
> > > everyone knows "IN" is pain fully slow currently
> > in pgsql
> > > is the any fster alternative to above query
> > >
> > > regds
> > > Mallah.
> > >
> > > ---------------------------(end of
> > broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
> > majordomo@postgresql.org
> >

Optimizer for IN (..) ? / Re: Thank you ! Re: Is there any Faster workaround ??

From

Bernd von den Brincken

Date:

15 April 2002, 11:58:33

Hello,

At 09.04.2002 23:32, Rajesh Kumar Mallah <mallah@trade-india.com> wrote:
>i think this question has been answered before also in
>this mailing list  sorry for asking...
>...
> > How about:
> >
> > DELETE  from t_a where exist (select email from t_b where t_b.email =
> > t_a.email)
> >
> > Rajesh Kumar Mallah wrote:
> > > ... We require to do below or similar quite often
> > >
> > > DELETE  from t_a where email in (select email from t_b);
> > >
> > > everyone knows "IN" is pain fully slow currently in pgsql
> > > is the any fster alternative to above query

Yes this question was posted before - and if so, and if the answer is so 
simple - would it not make sense to implement this wisdom in the optimizer?
Regards
// Bernd vdB

Re: Thank you ! Re: Is there any Faster workaround ??

From

Roberto Mello

Date:

15 April 2002, 14:42:35

On Wed, Apr 10, 2002 at 12:24:26PM +0200, Bernd von den Brincken wrote:

> >> > everyone knows "IN" is pain fully slow currently in pgsql
> >> > is the any fster alternative to above query
> 
> Yes this question was posted before - and if so, and if the answer is so 
> simple - would it not make sense to implement this wisdom in the optimizer?

The IN construct is slow in Oracle and probably other RDBMSs too. I don't
know if the PostgreSQL implementation is any slower than others (I don't
think it is).

-Roberto
-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net/      http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
 
The more things change, the more they stay the same.