From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query with backwards index scan
Date: Mon, 30 Jul 2007 18:30:50 +0200
Message-ID: <f8l3oc$6lp$1@sea.gmane.org> (raw)
In-Reply-To: <20070728205450.GI19392@tils.net>
References: <200707281903.l6SJ3qs07818@pillette.com>
<20070728192713.GH19392@tils.net>
<46ABA1AA.7020701@emolecules.com>
<20070728205450.GI19392@tils.net>
Tilmann Singer skrev:
> But the subselect is not fast for the user with many relationships and
> matched rows at the beginning of the sorted large_table:
>
> testdb=# EXPLAIN ANALYZE SELECT * FROM large_table lt
> WHERE user_id IN (SELECT contact_id FROM relationships WHERE
> user_id=55555)
> ORDER BY created_at DESC LIMIT 10;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=6963.94..6963.96 rows=10 width=621) (actual time=53187.349..53187.424 rows=10 loops=1)
> -> Sort (cost=6963.94..6966.96 rows=1211 width=621) (actual time=53187.341..53187.360 rows=10 loops=1)
> Sort Key: lt.created_at
> -> Nested Loop (cost=39.52..6901.92 rows=1211 width=621) (actual time=201.728..52673.800 rows=69018 loops=1)
> -> HashAggregate (cost=39.52..39.53 rows=1 width=4) (actual time=178.777..178.966 rows=40 loops=1)
> -> Bitmap Heap Scan on relationships (cost=4.33..39.49 rows=10 width=4) (actual time=47.049..178.560 rows=40 loops=1)
> Recheck Cond: (user_id = 55555)
> -> Bitmap Index Scan on relationships_user_id_contact_id_index (cost=0.00..4.33 rows=10 width=0) (actual time=28.721..28.721 rows=40 loops=1)
> Index Cond: (user_id = 55555)
> -> Index Scan using large_user_id_started_at_index on large_table lt (cost=0.00..6834.04 rows=2268 width=621) (actual time=21.994..1301.375 rows=1725 loops=40)
> Index Cond: (lt.user_id = relationships.contact_id)
> Total runtime: 53188.584 ms
>
>
>
> Using a join now the query for mat
> Any ideas?
It seems to me the subselect plan would benefit quite a bit from not
returning all rows, but only the 10 latest for each user. I believe the
problem is similar to what is discussed for UNIONs here:
http://groups.google.dk/group/pgsql.general/browse_thread/thread/4f74d7faa8a5a608/367f5052b1bbf1c8?l...
which got implemented here:
http://groups.google.dk/group/pgsql.committers/browse_thread/thread/b1ac3c3026db096c/9b3e5bd2d612f56...
It seems to me the planner in this case would actually need to push the
limit into the nested loop, since we want the plan to take advantage of
the index (using a backwards index scan). I am ready to be corrected though.
You could try this (quite hackish) attempt at forcing the query planner
to use this plan:
SELECT * FROM large_table lt
WHERE user_id IN (SELECT contact_id FROM relationships WHERE
user_id=55555) AND created_at in (SELECT created_at FROM large_table
lt2 WHERE lt2.user_id = lt.user_id ORDER BY created_at DESC limit 10)
ORDER BY created_at DESC LIMIT 10;
If that doesn't work, you might have reached the point where you need to
use some kind of record-keeping system to keep track of which records to
look at.
/Nis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nis@superlativ.dk
Subject: Re: Slow query with backwards index scan
In-Reply-To: <f8l3oc$6lp$1@sea.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox