pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Sub-select speed.
5+ messages / 3 participants
[nested] [flat]

* Sub-select speed.
@ 1999-12-17 18:50 Mitch Vincent <mitch@venux.net>
  1999-12-17 21:45 ` Re: [SQL] Sub-select speed. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Mitch Vincent @ 1999-12-17 18:50 UTC (permalink / raw)
  To: pgsql-sql

Hey guys, I have a few queries here that I would greatly appreciate any
pointers on. I ned to get them as fast as possible but would settle for ANY
speed improvement over what it is now..

select * from applicants as a where a.status = 'A' and a.app_id in(select
b.app_id from resume_search as b where a.app_id=b.app_id and b.user_id=291)

resume_search is a table populated by a query before this one.  The schema
is as follows :
| user_id                          | int4                             |
4 |
| app_id                           | int4                             |
4 |

Indices:  resume_app_id
              resume_search_id

So, in it's current form, with only a few (say 5) rows in resume_search,
it's still tking quite some time.

In addition to this, I have another query liek this one, only it inserts to
another table also..

insert into users_download_app (user_id,app_id) select app_id from
applicants where status = 'A' and app_id in(select b.app_id from
resume_search as b where app_id=b.app_id and b.user_id=291) limit 200

This one is so slow that it's unusable. (We're talking minutes here on my
Celeron 333 devel server).

I wish there was another way to get what I need to get done but the
sub-selects are the only way I can see doing it now. I have a feeling though
that there are some extra things that I might be able to do to get the same
results from the same tables, faster.

Thanks to one and all.

-Mitch








^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Sub-select speed.
  1999-12-17 18:50 Sub-select speed. Mitch Vincent <mitch@venux.net>
@ 1999-12-17 21:45 ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-12-17 22:05   ` Re: [SQL] Sub-select speed. Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 1999-12-17 21:45 UTC (permalink / raw)
  To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql

"Mitch Vincent" <mitch@venux.net> writes:
> select * from applicants as a where a.status = 'A' and a.app_id in(select
> b.app_id from resume_search as b where a.app_id=b.app_id and b.user_id=291)

WHERE ... IN (sub-select) is a very inefficient substitute for a plain join
query.  Try something like

select a.* from applicants as a, resume_search as b
where a.status = 'A' and a.app_id = b.app_id and b.user_id = 291;

Providing indexes on one or both app_id fields might help.  Also, don't
forget to do a VACUUM every so often to make sure the planner has useful
statistics about the sizes of the tables.

There has been some talk of automatically rewriting queries to eliminate
unnecessary sub-selects, but I don't foresee it getting done for a
while yet.

> insert into users_download_app (user_id,app_id) select app_id from
> applicants where status = 'A' and app_id in(select b.app_id from
> resume_search as b where app_id=b.app_id and b.user_id=291) limit 200

BTW, there's a bug in current sources: LIMIT is ignored by INSERT
... SELECT.  I think it works OK to do SELECT INTO ... LIMIT, however,
and then you could insert into the final destination table from the
INTO temp table.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Sub-select speed.
  1999-12-17 18:50 Sub-select speed. Mitch Vincent <mitch@venux.net>
  1999-12-17 21:45 ` Re: [SQL] Sub-select speed. Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-12-17 22:05   ` Bruce Momjian <pgman@candle.pha.pa.us>
  1999-12-17 23:25     ` Re: [SQL] Sub-select speed. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Bruce Momjian @ 1999-12-17 22:05 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mitch Vincent <mitch@venux.net>; pgsql-sql

> "Mitch Vincent" <mitch@venux.net> writes:
> > select * from applicants as a where a.status = 'A' and a.app_id in(select
> > b.app_id from resume_search as b where a.app_id=b.app_id and b.user_id=291)
> 
> WHERE ... IN (sub-select) is a very inefficient substitute for a plain join
> query.  Try something like
> 
> select a.* from applicants as a, resume_search as b
> where a.status = 'A' and a.app_id = b.app_id and b.user_id = 291;
> 
> Providing indexes on one or both app_id fields might help.  Also, don't
> forget to do a VACUUM every so often to make sure the planner has useful
> statistics about the sizes of the tables.
> 
> There has been some talk of automatically rewriting queries to eliminate
> unnecessary sub-selects, but I don't foresee it getting done for a
> while yet.


Tom, you mentioned that subselects use nested join, but they could be
hardcoded to use hash join.  My opinion is that this should be done if
it is easy.  I know the concern was that a hash could overflow if the
subquery is too larger, but if the subquery is too large, nested join
will take forever, so it really doesn't matter if it completes or not.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Sub-select speed.
  1999-12-17 18:50 Sub-select speed. Mitch Vincent <mitch@venux.net>
  1999-12-17 21:45 ` Re: [SQL] Sub-select speed. Tom Lane <tgl@sss.pgh.pa.us>
  1999-12-17 22:05   ` Re: [SQL] Sub-select speed. Bruce Momjian <pgman@candle.pha.pa.us>
@ 1999-12-17 23:25     ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-12-18 00:32       ` Re: [SQL] Sub-select speed. Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 1999-12-17 23:25 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mitch Vincent <mitch@venux.net>; pgsql-sql

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> There has been some talk of automatically rewriting queries to eliminate
>> unnecessary sub-selects, but I don't foresee it getting done for a
>> while yet.

> Tom, you mentioned that subselects use nested join, but they could be
> hardcoded to use hash join.  My opinion is that this should be done if
> it is easy.

That would probably help for uncorrelated subselects (where no variable
from the outer query is referenced in the inner one).  For correlated
subselects, such as this one is, hashing the subselect result isn't
going to help much --- the real problem is that the subselect is
repeated afresh for each outer tuple.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Sub-select speed.
  1999-12-17 18:50 Sub-select speed. Mitch Vincent <mitch@venux.net>
  1999-12-17 21:45 ` Re: [SQL] Sub-select speed. Tom Lane <tgl@sss.pgh.pa.us>
  1999-12-17 22:05   ` Re: [SQL] Sub-select speed. Bruce Momjian <pgman@candle.pha.pa.us>
  1999-12-17 23:25     ` Re: [SQL] Sub-select speed. Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-12-18 00:32       ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Bruce Momjian @ 1999-12-18 00:32 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mitch Vincent <mitch@venux.net>; pgsql-sql

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> There has been some talk of automatically rewriting queries to eliminate
> >> unnecessary sub-selects, but I don't foresee it getting done for a
> >> while yet.
> 
> > Tom, you mentioned that subselects use nested join, but they could be
> > hardcoded to use hash join.  My opinion is that this should be done if
> > it is easy.
> 
> That would probably help for uncorrelated subselects (where no variable
> from the outer query is referenced in the inner one).  For correlated
> subselects, such as this one is, hashing the subselect result isn't
> going to help much --- the real problem is that the subselect is
> repeated afresh for each outer tuple.

Oh, I was thinking in general of our EXISTS() workaround and if we could
help that by forcing hash joins.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~1999-12-18 00:32 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-17 18:50 Sub-select speed. Mitch Vincent <mitch@venux.net>
1999-12-17 21:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-17 22:05   ` Bruce Momjian <pgman@candle.pha.pa.us>
1999-12-17 23:25     ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-18 00:32       ` Bruce Momjian <pgman@candle.pha.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox