On Fri, Oct 09, 2009 at 08:31:54PM +0800, Xia Qingran wrote: > On Mon, Oct 5, 2009 at 9:58 AM, Omar Kilani <omar.kilani@xxxxxxxxx> wrote: > > Hi Xia, > > > > Try this patch: > > > > http://treehou.se/~omar/postgresql-8.4.1-array_sel_hack.patch > > > > It's a hack, but it works for us. I think you're probably spending > > most of your query time planning, and this patch helps speed things up > > 10x over here. > > Thanks! > I am trying it. > > Regards, > > Xia Qingran > We have a similar situation when using DSPAM with a PostgreSQL backend. In that case we used a function like the following to speed up the lookups. I do not know if it would be useful in your situation, but I thought I would post it for the group: The original query was of the form: SELECT uid, token, spam_hits, innocent_hits FROM dspam_token_data WHERE uid = 'xxx' AND token IN (...); The faster version of the query in the current code is: SELECT * FROM lookup_tokens(%d, '{...}); where lookup_tokens is defined as follows: create function lookup_tokens(integer,bigint[]) returns setof dspam_token_data language plpgsql stable as ' declare v_rec record; begin for v_rec in select * from dspam_token_data where uid=$1 and token in (select $2[i] from generate_series(array_lower($2,1), array_upper($2,1)) s(i)) loop return next v_rec; end loop; return; end;'; Anyway, you may want to try a similar approach instead of the posted code change. Regards, Ken > > > > Regards, > > Omar > > > > On Sun, Sep 27, 2009 at 5:13 PM, Xia Qingran <qingran.xia@xxxxxxxxx> wrote: > >> On Sat, Sep 26, 2009 at 10:59 PM, Craig James > >> <craig_james@xxxxxxxxxxxxxx> wrote: > >>> > >>> If your user_id is always in a narrow range like this, or even in any range > >>> that is a small fraction of the total, then add a range condition, like > >>> this: > >>> > >>> select * from event where user_id <= 500 and user_id >= 0 and user_id in > >>> (...) > >>> > >>> I did this exact same thing in my application and it worked well. > >>> > >>> Craig > >>> > >> > >> It is a good idea. But In my application, most of the queries' user_id > >> are random and difficult to range. > >> Thanks anyway. > >> > >> > >> > >> -- > >> ????????? > >> Xia Qingran > >> qingran.xia@xxxxxxxxx > >> Sent from Beijing, 11, China > >> Charles de Gaulle ??- "The better I get to know men, the more I find > >> myself loving dogs." - > >> http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html > >> > >> -- > >> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-performance > >> > > > > > > -- > ????????? > Xia Qingran > qingran.xia@xxxxxxxxx > Sent from Beijing, 11, China > Stephen Leacock - "I detest life-insurance agents: they always argue > that I shall some day die, which is not so." - > http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
