I believe his earlier (original) complaint was that it was slower with the LIMIT than with no limit. As in, the select (*) query was faster to get the whole thing than apply the limit. Wherever that is the case, it is broken. Certainly a complex join makes this more difficult, but one would agree that with a LIMIT it should never be slower than without, right? Any LIMIT combined with an ORDER by at the last stage can't be completed without fetching every row and sorting. If there is an index on the column being sorted at each table in the join it is possible to short-circuit the plan after enough result rows are found, but this would have to iterate on each arm of the join until there were enough matches, and postgres does not have any such iterative query strategy that I'm aware of. For a single large, indexed table with no joins it certainly makes sense to terminate the index scan early. Often, one is better off with explicit subselects for the join branches with explicit LIMIT on each of those (oversized to compensate for the likelihood of a match) but you have to be willing to get less than the LIMIT ammount if there are not enough matches, and that is usually bad for 'paging' style queries rather than 'report on top X' queries where the premature truncation is probably ok and is enough to make the query fast. But if you have very large datasets on the query branch arms, asking for only 10K of 300K rows to sort and then match against 10K in another arm, and find 500 items, can be a huge gain versus doing the entire thing. -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Scott Marlowe Sent: Monday, November 24, 2008 11:23 AM To: Andrus Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: limit clause produces wrong query plan On Mon, Nov 24, 2008 at 10:26 AM, Andrus <kobruleht2@xxxxxx> wrote: >> it was veery fast. To be honest I do not know what is happening?! > > This is really weird. > It seems that PostgreSql OFFSET / LIMIT are not optimized and thus typical > paging queries And how exactly should it be optimized? If a query is even moderately interesting, with a few joins and a where clause, postgresql HAS to create the rows that come before your offset in order to assure that it's giving you the right rows. > SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET pageno*100 LIMIT 100 This will get progressively slower as pageno goes up. > SELECT ... FROM bigtable ORDER BY intprimarykey OFFSET 0 LIMIT 100 That should be plenty fast. > cannot be used in PostgreSql at all for big tables. Can't be used in any real database with any real complexity to its query either. > Do you have any idea how to fix this ? A standard workaround is to use some kind of sequential, or nearly so, id field, and then use between on that field. select * from table where idfield between x and x+100; -- 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
