1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Rep Power

    OFFSET and subselects

    Hi all,

    I recently noticed a behaviour which seems quite suboptimal - I am working on a "mart" type application, which in practice means I end up with queries which have a few filters on several central tables, and then a few dozen subselects for other info (which seems to perform better than several dozen equivalent LEFT JOINs do). I am running in trouble with pagination here, somehow (rather naively) I assumed that when doing a LIMIT and OFFSET, the subselects on the records before the OFFSET would not be performed, which quite apparently is not the case. So, LIMIT 50 OFFSET 0 takes 100ms to run, LIMIT 50 OFFSET 50 takes 200ms, LIMIT 50 OFFSET 100 takes 300ms; and so forth, this really becomes unacceptable after a few pages.

    I was wondering how I migh improve the situation? Two possibilities come to mind: a) run the query without the subselects and store the LIMIT 50 in a temp table, doing a select all from that with the subselects as a separate query or b) similar, but run the queries from the subselects as separate queries, assembling everything in the app.

    Is one better than the other in any way? Are there better ways to deal with this?

  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Central Florida, USA
    Rep Power
    Where in the query is your subquery?

    1. in the WHERE clause? (not recommended)

    2. as part of the SELECT

    3. in the FROM clause

    Generally subselects in the WHERE clause are what cause the most performance problems, and are the most avoidable if you re-think your query.

    How about posting your actual query or queries here, and then we'll have a better idea how to help you.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Rep Power
    and if you are using a new enough version of postgresql you can do explain plan or explain query to tell you how many "points" your query will cost; you can try different configurations of the subquery and see how they compare when interpreted by the backend.

IMN logo majestic logo threadwatch logo seochat tools logo