pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mitch Vincent <mitch@venux.net>
To: pgsql-sql@postgreSQL.org
Subject: Sub-select speed.
Date: Fri, 17 Dec 1999 13:50:06 -0500
Message-ID: <018301bf48bf$8a068620$0300000a@doot.org> (raw)
References: <17438.945241027@sss.pgh.pa.us>

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








view thread (5+ messages)  latest in thread

Message-ID: <018301bf48bf$8a068620$0300000a@doot.org>
Permalink:  ../018301bf48bf$8a068620$0300000a@doot.org/
Also on:    postgresql.org/message-id/018301bf48bf$8a068620$0300000a@doot.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mitch@venux.net
  Subject: Re: Sub-select speed.
  In-Reply-To: <018301bf48bf$8a068620$0300000a@doot.org>
  References: <17438.945241027@sss.pgh.pa.us> <018301bf48bf$8a068620$0300000a@doot.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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