From: | Chris Gamache <cgg007(at)yahoo(dot)com> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: improving performance of UNION and ORDER BY |
Date: | 2002-03-07 13:56:28 |
Message-ID: | 20020307135628.59688.qmail@web13804.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I think I've found an acceptable compromise. The optimizer will use the index
if I limit each of the subqueries to no more than 6200 rows. I think a
composite total ~12000 is enough data, understanding that the users of this
query would be silly to use it for paging through more than a week's data. I
think it'd be silly to go beyond 24 hours... Anyway, if they're looking for
something more specific, I take out the limits to allow it search through the
entire database, and unless they're not using specific enough search criteria
the query will still move rather quickly. A decent compromise.
Thanks for helping me nail this one down!
CG
--- Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
> On Wed, 6 Mar 2002, Chris Gamache wrote:
>
> > UNION ALL was an excellent idea! It didn't cut much time off, but at least
> no
> > resources are devoted to eliminating the nonexistant duplicate rows.
> >
> > I've had days to think about this. It seems as though the ORDER BY part of
> the
> > first query is the culprit. When I run this query by itself, I can see that
> it
> > would comprise the bulk of the UNION query time.
> >
> > select
> > a.username as "User",
> > a.trans_date as "Date",
> > tl.longtype as "Type",
> > a.trans_data as "Query Data",
> > a.trans_charge as "Charged",
> > a.user_reference_id as "Reference ID"
> > from a_trans_log a
> > join addtypelong tl on a.trans_type = tl.shorttype
> > where a.trans_date >= '12/31/01'::TIMESTAMP
> > order by a.trans_date desc, a.trans_data limit 20;
> >
> > By removing the ORDER BY a.trans_data, it cut the query down to the "almost
> > instant" level... EXPLAIN shows me that it uses the indeces! I guess I need
> to
> > drop that part of the ORDER BY, or make an index for it to use... Bah.
>
> Yeah, although since you're doing different kinds of scans on the two
> columns (desc and asc), I'm not sure what'd be necessary to get the index
> used. Normally you'd just make an index on trans_date,trans_data, but
> that won't work here. I think Tom Lane may have discussed a way to get
> the index scan for that case sometime in the past couple of months on the
> mailing list.
>
> > Alas...
> > Unless someone knows different, I don't believe that I can use the LIMIT
> > statement in each of the subqueries. The app needs to page through the
> > UNIONized table. As the app pages through LIMIT 20,20 LIMIT 20,40 LIMIT
> 20,60,
> > etc. It needs to look at the whole sorted UNIONized table. (am I making ANY
> > sense?)
>
> That makes sense. You could cheat a little if you knew how large the
> results sets for the parts were if you're using union all since I believe
> it will do them in order and just append the sets, but that doesn't seem
> like a good idea in general.
>
> > Even by dropping the order on column 4, it still takes 6 seconds to
> assemble
> > the data, sort it and limit it... Could I create a cross-table index
> > specifically for this query? I doubt its a) possible, b) (even if possible)
> a
> > good idea.
>
> I don't think so.
>
> One question is whether some of this could be done by keeping another
> table around that you do your limits through. This would at least cut
> down some of the cost (or for that matter if you could make a table with
> this using triggers and such).
>
>
__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Self | 2002-03-07 14:23:07 | Re: Setting up Field constraints |
Previous Message | Thomas Lockhart | 2002-03-07 13:48:13 | Re: Index doesn't appear to be working. |