Denis de Bernardy <ddebernardy@xxxxxxxxx> writes: > An alternative plan could have been to hash join the tables together, > to sort the result set, and to apply the limit/offset on the resulting > set. Indeed. I rather wonder why the planner didn't do that to start with. This plan looks to me like it might be suffering from insufficient work_mem to allow use of a hash join. Or possibly the OP changed some of the cost_xxx or enable_xxx settings in a misguided attempt to force it to use indexes instead. As a rule of thumb, whole-table joins probably ought not be using nestloop plans, and that frequently means that indexes are worthless for them. But in any case, as Craig noted, the real elephant in the room is the huge OFFSET value. It seems likely that this query is not standing alone but is meant as one of a series that's supposed to provide paginated output, and if so the total cost of the series is just going to be impossible no matter what. The OP needs to think about using a cursor or some such to avoid repeating most of the work each time. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
