Message-ID: <42D6F7CE.5010007@arbash-meinel.com>
Date: Thu, 14 Jul 2005 18:39:58 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Macintosh/20050317)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Dan Harris <fbsd@drivefaster.net>,
	pgsql-performance@postgresql.org
Subject: Re: slow joining very large table to smaller ones
References: <46AF8F35-B735-4DFA-BA0A-AC82B5DA3CCE@drivefaster.net>
	<42D687E5.7080202@arbash-meinel.com>
	<C3470621-9DB0-444A-BAD1-F9995E0142FB@drivefaster.net>
	<42D6F177.3000002@arbash-meinel.com>
	<5708.1121383802@sss.pgh.pa.us>
In-Reply-To: <5708.1121383802@sss.pgh.pa.us>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig20905F1FC17C14B2B7F6EFD9"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig20905F1FC17C14B2B7F6EFD9
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> John A Meinel <john@arbash-meinel.com> writes:
>
>>What I don't understand is that the planner is actually estimating that
>>joining against the new table is going to *increase* the number of
>>returned rows.
>
>
> It evidently thinks that incidentid in the k_r table is pretty
> nonunique.  We really need to look at the statistics data to
> see what's going on.
>
> 			regards, tom lane
>

Okay, sure. What about doing this, then:

EXPLAIN ANALYZE
SELECT recordtext FROM eventactivity
  JOIN (SELECT DISTINCT incidentid FROM k_r JOIN k_b USING (incidentid)
	 WHERE k_r.id = ?? AND k_b.id = ??)
 USING (incidentid)
;

Since I assume that eventactivity is the only table with "recordtext",
and that you don't get any columns from k_r and k_b, meaning it would be
pointless to get duplicate incidentids.

I may be misunderstanding what the query is trying to do, but depending
on what is in k_r and k_b, is it possible to use a UNIQUE INDEX rather
than just an index on incidentid?

There is also the possibility of
EXPLAIN ANALYZE
SELECT recordtext FROM eventactivtity
  JOIN (SELECT incidentid FROM k_r WHERE k_r.id = ??
         UNION SELECT incidentid FROM k_b WHERE k_b.id = ??)
 USING (incidentid)
;

But both of these would mean that you don't actually want columns from
k_r or k_b, just a unique list of incident ids.

But first, I agree, we should make sure the pg_stats values are reasonable.

John
=:->


--------------enig20905F1FC17C14B2B7F6EFD9
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC1vfPJdeBCYSNAAMRAgTWAJoDylT5gpCS6knbpQ0ztcboKlp/GACgsWSb
zm/lpqNKxkD6fOywBwCNIls=
=n7aS
-----END PGP SIGNATURE-----

--------------enig20905F1FC17C14B2B7F6EFD9--