Re: Slow query with sub-select

From: Rick Genter <rick(dot)genter(at)gmail(dot)com>
To:
Cc: Rick Genter <rick(dot)genter(at)gmail(dot)com>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Slow query with sub-select
Date: 2011-07-16 23:40:10
Message-ID: EEAAD9C0-81B6-4B63-8AAB-00D3AD1B7388@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


On Jul 16, 2011, at 4:14 PM, - - wrote:

> I would like to count rows in q whose mid does not exist in t.

I would write such a query like this:

SELECT COUNT(*)
FROM q
LEFT OUTER JOIN t
ON (t.mid = q.mid)
WHERE t.mid IS NULL;

And I would make sure there was an index on t.mid. (And for 9.2, as I understand it, q.mid as well, since I believe in 9.2 PostgreSQL will be able to compute the result strictly from the indexes without hitting the base tables.)

--
Rick Genter
rick(dot)genter(at)gmail(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message AI Rumman 2011-07-17 09:19:19 "FATAL: role "postgres" does not exist" after recover
Previous Message - - 2011-07-16 23:14:38 Re: Slow query with sub-select