pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedInteresting Query Performance Question
4+ messages / 3 participants
[nested] [flat]
* Interesting Query Performance Question
@ 2010-11-06 15:01 Jonathan Hoover <jhoover@yahoo-inc.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jonathan Hoover @ 2010-11-06 15:01 UTC (permalink / raw)
To: pgsql-admin
Any explanation for this is appreciated...
I had a query which just seemed to peg the processor. I decided to let it go all night, and after 16 hours, it was still stuck at 100% processor usage. I figured something must be wrong, so I tried a few things. Using the following logic, I was able to get the results I was looking for in about 1 minute (instead of 16+ hours of no result). It all came down to getting around the use of "NOT IN (subquery)" where "subquery" has 20,000,000 rows. "IN (subquery)" was quick as could be. Here's what I found.
Tables:
------------------------------
| a | b |
--------------|---------------
|guid vc(32) | guid vc(32) |
|unitid vc(15)| unitid vc(15)|
|ftime ts | |
|source vc(10)| |
------------------------------
|65,000,000 rw| 500,000 rows |
------------------------------
Here is the query that hung up for 16 hours:
A:
SELECT unitid,count(guid) AS total FROM b
WHERE guid NOT IN (SELECT DISTINCT guid FROM a) AND
unitid IN (SELECT DISTINCT unitid FROM a)
GROUP BY unitid
ORDER BY total DESC;
I thought I'd narrow down a.guid and a.unitid and substitute these for the subqueries.
B: SELECT DISTINCT unitid INTO a_u_unitid FROM a; ( 1,082 rows)
C: SELECT DISTINCT guid INTO a_u_guid FROM a; (20,000,000 rows)
That didn't help on its own. So I decided to try the query without the "NOT". It finished in a minute or two. Granted, the result is the opposite of what I wanted, but at least I knew the major source of the problem. I still had the substituted queries B & C in, so it looked like this:
D:
SELECT unitid,count(guid) AS total FROM b
WHERE guid IN (SELECT guid FROM a_u_guid) AND
unitid IN (SELECT unitid FROM a_u_unitid)
GROUP BY unitid
ORDER BY total DESC;
Next, I decided to just get my "NOT" set of guid's from b itself. If my logic isn't correct here, someone let me know. I did this, and got results very quickly (a minute perhaps):
E:
SELECT unitid,count(guid) AS total FROM b
WHERE guid NOT IN (SELECT DISTINCT guid FROM b WHERE guid IN (SELECT guid FROM a_u_guid)) AND
unitid IN (SELECT unitid FROM a_u_unitid)
GROUP BY unitid
ORDER BY total DESC;
Keep in mind a has 65,000,000 rows and b only has 500,000. The above query (E) gave me the original results I was looking for (E gives the expected results from A, if A had ever finished, assuming my logic is correct). I then decided to get rid of the intermediate tables B & C, so I was back to:
F:
SELECT unitid,count(guid) AS total FROM b
WHERE guid NOT IN (SELECT DISTINCT guid FROM b WHERE guid IN (SELECT DISTINCT guid FROM a) AND
Unitid IN (SELECT DISTINCT unitid FROM a)
GROUP BY unitid
ORDER BY total DESC;
While F was running, I started typing this email. It still has not finished, but I have a feeling it will within another half hour or so. What I am taking away from this is that the use of "NOT IN (subquery)" where subquery has a large number of results will literally shut down performance and never give a result. Further, I am taking away that creating separate tables with just the DISTINCT values from a table with millions of rows also saves time. The problem here being that the separate table is not auto-updating when values in the master table change.
Query E then is apparently the way to go, but shouldn't there be a way to get the query planner to take these steps itself? If A had ever finished, I'd sure like to have seen an EXPLAIN ANALYZE on it. If F does finish, I made run an EXPLAIN ANALYZE on it versus E (I kind of expected F to finish while writing this email).
Thanks,
Jon
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Interesting Query Performance Question
@ 2010-11-06 15:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jonathan Hoover <jhoover@yahoo-inc.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-11-06 15:59 UTC (permalink / raw)
To: Jonathan Hoover <jhoover@yahoo-inc.com>; +Cc: pgsql-admin
"Jonathan Hoover" <jhoover@yahoo-inc.com> writes:
> [ poor performance with NOT IN ]
> Query E then is apparently the way to go, but shouldn't there be a way
> to get the query planner to take these steps itself? If A had ever
> finished, I'd sure like to have seen an EXPLAIN ANALYZE on it.
Well, just an EXPLAIN would have told you what the plan was like.
What I suspect was happening was that your manipulations of the query
altered the planner's estimate of the number of rows in the NOT IN's
subquery, causing it to pick (or not) a hash-table-based implementation
of NOT IN. The hashed approach is a lot faster but requires the
subquery's output to fit in work_mem.
In general, NOT IN is hard to optimize because of its weird behavior
for NULLs. I'd suggest looking into converting the query to use an
EXISTS instead.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Interesting Query Performance Question
@ 2010-11-06 16:53 David W Noon <dwnoon@ntlworld.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: David W Noon @ 2010-11-06 16:53 UTC (permalink / raw)
To: pgsql-admin
On Sat, 06 Nov 2010 11:59:48 -0400, Tom Lane wrote about Re: [ADMIN]
Interesting Query Performance Question:
>In general, NOT IN is hard to optimize because of its weird behavior
>for NULLs. I'd suggest looking into converting the query to use an
>EXISTS instead.
I have know about that issue for some years, so I always use a column
that has been declared NOT NULL for the sub-query. Does PostgreSQL not
use that property to bypass the vagaries of NULL in an IN predicate?
--
Regards,
Dave [RLU #314465]
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
dwnoon@ntlworld.com (David W Noon)
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Interesting Query Performance Question
@ 2010-11-06 17:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: David W Noon <dwnoon@ntlworld.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-11-06 17:12 UTC (permalink / raw)
To: David W Noon <dwnoon@ntlworld.com>; +Cc: pgsql-admin
David W Noon <dwnoon@ntlworld.com> writes:
> On Sat, 06 Nov 2010 11:59:48 -0400, Tom Lane wrote about Re: [ADMIN]
>> In general, NOT IN is hard to optimize because of its weird behavior
>> for NULLs. I'd suggest looking into converting the query to use an
>> EXISTS instead.
> I have know about that issue for some years, so I always use a column
> that has been declared NOT NULL for the sub-query. Does PostgreSQL not
> use that property to bypass the vagaries of NULL in an IN predicate?
No. It's a reasonable TODO item though. Up till fairly recently, we
didn't have infrastructure that would allow building plans that depended
for correctness on the existence of a constraint. (In particular,
I'd want such a plan to get thrown away and redone if someone dropped
the NOT NULL constraint.) We have most of that now, although it only
works for constraints that have entries in pg_constraint, which NOT NULL
doesn't yet. Maybe in another few months ...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-11-06 17:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-06 15:01 Interesting Query Performance Question Jonathan Hoover <jhoover@yahoo-inc.com>
2010-11-06 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-06 16:53 ` David W Noon <dwnoon@ntlworld.com>
2010-11-06 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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