Re: Query got slow from 9.0 to 9.1 upgrade

On Tue, May 1, 2012 at 12:17 AM, Josh Turmel <jturmel(at)gmail(dot)com> wrote:
> We just upgraded from 9.0 to 9.1, we're using the same server configuration,
> that has been confirmed 3 or 4 times over. Any help would be appreciated. If
> I remove the "ORDER BY" it gets fast again because it goes back to using the
> user_id index, if I remove the LIMIT/OFFSET it gets fast again, obviously I
> need both of those, but that was just to test and see what would happen.
>
> Query: SELECT * FROM bookmark_groups WHERE user_id = 6708929 ORDER BY
> created DESC LIMIT 25 OFFSET 0;

Based on the explain numbers I'd say that 9.0 was fast by accident of
having inaccurate statistics. You can see that 9.0 estimated that 757
rows have this user_id, while actually it had 33868 rows. 9.1
estimated a more accurate 35980 rows, and because of that assumed that
reading the newest created rows would return 25 rows of this user
rather fast, faster than sorting the 35980 rows. This assumption seems
to be incorrect, probably because the rows with this user_id are all
rather old.

You could try tweaking cpu_index_tuple_cost to be higher so that large
index scans get penalized. But ultimately with the current PG version
there isn't a good general way to fix this kind of behavior. You can
rewrite the query to enforce filtering before sorting:

SELECT * FROM (
SELECT * FROM bookmark_groups WHERE user_id = 6708929
OFFSET 0 -- Prevents pushdown of ordering and limit
) AS sub ORDER BY created DESC LIMIT 25 OFFSET 0;

This is the same issue that Simon Riggs talks about in this e-mail:
http://archives.postgresql.org/message-id/CA+U5nMLbXfUT9cWDHJ3tpxjC3bTWqizBKqTwDgzebCB5bAGCgg@mail.gmail.com

The more general approach is to be more pessimistic about limited
filtered index-scans, or collecting multi-dimensional stats to figure
out the correlation that all rows for this user are likely to be old.

Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de