| From: | André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Gregory Stark <stark(at)enterprisedb(dot)com>, PostgreSQL <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Hash join in 8.3 |
| Date: | 2007-12-13 21:29:38 |
| Message-ID: | 4761A442.8090600@ecomtecnologia.com.br |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane escreveu:
<blockquote cite="mid:18544(dot)1197574622(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Gregory Stark <a class="moz-txt-link-rfc2396E" href="mailto:stark(at)enterprisedb(dot)com"><stark(at)enterprisedb(dot)com></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">But I'm curious if you turn off mergejoin whether you can get a Nested Loop
plan and what cost 8.3 gives it. It looks to me like 8.3 came up with a higher
cost for Nested Loop than 8.1.9 (I think 8.1.10 came out with some planner
fixes btw) and so it's deciding these other plans are better. And they might
have been better for the imaginary scenario that the planner thinks is going
on.
</pre>
</blockquote>
<pre wrap=""><!---->
Actually, now that I think about it, 8.3 should be *more* likely than
8.1 to choose a nestloop-with-inner-indexscan plan. 8.1 didn't have the
changes to allow a discount for repeated inner indexscans.
I'm wondering if
(a) the 8.1 installation being compared to had some planner cost
parameter changes that were not copied into the 8.3 installation; or
(b) the only reason 8.1 likes the nestloop plan is that it has no
statistics on the test tables, whereas 8.3 does have stats because
of autovacuum being on by default.
regards, tom lane
</pre>
</blockquote>
<br>
I think I found the answer!<br>
<br>
8.1: likes nested loop even after vacuumdb on the database.<br>
<br>
8.3: likes hash at first time but:<br>
- after vacuumdb *on the database* (I was running on the tables.....),
it turns out to:<br>
Merge Join (cost=178779.93..328503.44 rows=30000 width=38) in
20005.207 ms<br>
#set enable_mergejoin=off;<br>
Hash Join (cost=156644.00..365204.03 rows=30000 width=38) in
29104.390 ms<br>
* a very faster hash here, seqscanning the smaller table before the
bigger one. Tricky!<br>
<br>
I wont trust table vacuums anymore...<br>
<br>
<pre class="moz-signature" cols="72">--
[]´s,
André Volpato
Ecom Tecnologia LTDA - Análise e Desenvolvimento
<a class="moz-txt-link-abbreviated" href="mailto:andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br">andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br</a></pre>
</body>
</html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.3 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Reg Me Please | 2007-12-13 21:31:42 | Re: COPY speedup |
| Previous Message | Gregory Stark | 2007-12-13 21:24:40 | Re: Hash join in 8.3 |