Mario Splivalo <mario.splivalo@xxxxxxxxx> 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
