From: Tom Lane <tgl@sss.pgh.pa.us>
To: mario.splivalo@mobart.hr
Cc: pgsql-performance@postgresql.org
Subject: Re: Speedup hint needed, if available? :)
Date: Tue, 30 May 2006 11:05:55 -0400
Message-ID: <23931.1149001555@sss.pgh.pa.us> (raw)
In-Reply-To: <1148998681.18422.24.camel@localhost.localdomain>
References: <1148998681.18422.24.camel@localhost.localdomain>
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> Here is the query which gets information on particular user, shows
> subscriptions to mailinglists and available credits on those
> mailinglists:
> SELECT u.subscription_id, u.user_id, u.mailinglist_id, u.valid_from,
> u.valid_to, sum(credits.credits_given - credits.credits_taken)::integer
> AS credits
> FROM user_subscriptions u
> LEFT JOIN
> (SELECT user_subscription_credits_given.subscription_id,
> user_subscription_credits_given.credits AS credits_given, 0 AS
> credits_taken
> FROM user_subscription_credits_given
> UNION ALL
> SELECT user_subscription_credits_taken.subscription_id, 0 AS
> credits_given, user_subscription_credits_taken.credits AS credits_taken
> FROM user_subscription_credits_taken) credits
> ON u.subscription_id = credits.subscription_id
> where
> u.user_id = 1
> GROUP BY u.subscription_id, u.user_id, u.mailinglist_id, u.valid_from,
> u.valid_to
Do you have realistic test data? The EXPLAIN shows that this is pulling
275366 of the 826032 rows in the two tables, which seems like rather a
lot for a single user. If it's reasonable that the query needs to fetch
one-third of the data, then you should resign yourself to it taking
awhile :-(
If the expected number of matching rows were much smaller, it would
make sense to use indexscans over the two big tables, but unfortunately
existing PG releases don't know how to generate an indexscan join
with a UNION ALL in between :-(. FWIW, 8.2 will be able to do it.
In current releases the only thing I can suggest is to merge
user_subscription_credits_given and user_subscription_credits_taken
into one table so you don't need the UNION ALL.
regards, tom lane
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: tgl@sss.pgh.pa.us, mario.splivalo@mobart.hr
Subject: Re: Speedup hint needed, if available? :)
In-Reply-To: <23931.1149001555@sss.pgh.pa.us>
* 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