From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query with backwards index scan
Date: Fri, 27 Jul 2007 20:28:52 +0200
Message-ID: <f8ddhg$3fh$1@sea.gmane.org> (raw)
In-Reply-To: <20070727172703.GG25252@tils.net>
References: <20070727172703.GG25252@tils.net>
Tilmann Singer skrev:
> The query works fine for the common cases when matching rows are found
> early in the sorted large table, like this:
>
> testdb=# EXPLAIN ANALYZE
> SELECT * FROM large_table lt
> LEFT JOIN relationships r ON lt.user_id=r.contact_id
> WHERE r.user_id = 55555 OR lt.user_id = 55555
> ORDER BY lt.created_at DESC LIMIT 10;
> QUERY PLAN
> but for the following user_id there are 3M rows in the large table
> which are more recent then the 10th matching one. The query then does
> not perform so well:
>
>
> testdb=# EXPLAIN ANALYZE
> SELECT * FROM large_table lt
> LEFT JOIN relationships r ON lt.user_id=r.contact_id
> WHERE r.user_id = 12345 OR lt.user_id = 12345
> ORDER BY lt.created_at DESC LIMIT 10;
> QUERY PLAN
> When split it up into the two following queries it performs much
> better for that user_id. Since the results of the two could be
> combined into the desired result, I'm assuming it could also be done
> efficiently within one query, if only a better plan would be used.
>
>
> testdb=# EXPLAIN ANALYZE
> SELECT * FROM large_table lt
> WHERE lt.user_id = 12345
> ORDER BY lt.created_at DESC LIMIT 10;
> QUERY PLAN
> testdb=# EXPLAIN ANALYZE
> SELECT * FROM large_table lt
> WHERE user_id IN (SELECT contact_id FROM relationships WHERE user_id=12345)
> ORDER BY created_at DESC LIMIT 10;
> QUERY PLAN
> I'm not very experienced reading query plans and don't know how to go
> about this from here - is it theoretically possible to have a query
> that performs well with the given data in both cases or is there a
> conceptual problem?
How does the "obvious" UNION query do - ie:
SELECT * FROM (
SELECT * FROM large_table lt
WHERE lt.user_id = 12345
UNION
SELECT * FROM large_table lt
WHERE user_id IN (SELECT contact_id FROM relationships WHERE user_id=12345)
) q
ORDER BY created_at DESC LIMIT 10;
?
How about
SELECT * FROM large_table lt
WHERE lt.user_id = 12345 OR user_id IN (SELECT contact_id FROM
relationships WHERE user_id=12345)
ORDER BY created_at DESC LIMIT 10;
?
I am missing a unique constraint on (user_id, contact_id) - otherwise
the subselect is not equivalent to the join.
Probably you also should have foreign key constraints on
relationships.user_id and relationships.contact_id. These are unlikely
to affect performance though, in my experience.
It might be good to know whether contact_id = user_id is possible -
since this would rule out the possibility of a row satisfying both
branches of the union.
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: <f8ddhg$3fh$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