Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1084)
Subject: Re: Subquery flattening causing sequential scan 
From: Jim Crate <jimcfl@gmail.com>
In-Reply-To: <12106.1325009566@sss.pgh.pa.us>
Date: Wed, 28 Dec 2011 12:22:49 -0500
Content-Transfer-Encoding: quoted-printable
Message-Id: <4BCFFC7E-795D-4886-ABB0-D7628E583999@gmail.com>
References: <0E417F76-FE21-4FB5-8132-0C485778A041@gmail.com>
	<12106.1325009566@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org

On Dec 27, 2011, at 1:12 PM, Tom Lane wrote:

> Jim Crate <jimcfl@gmail.com> writes:
>> My question is why does it do a seq scan when it flattens this
>> subquery into a JOIN?
>=20
> Because it thinks there will be 3783 rows out of the msg scan, which =
if
> true would make your desired nestloop join a serious loser.  You need =
to
> see about getting that estimate to be off by less than three orders of
> magnitude.  Possibly raising the stats target on emsg_messages would
> help.  I'd also try converting the inner NOT IN into a NOT EXISTS, =
just
> to see if that makes the estimate any better. =20


The planner does choose the nested loop after converting the NOT IN to =
NOT EXISTS.  Using LEFT JOIN / IS NULL also generated the same plan as =
NOT EXISTS.  I guess I really need to learn more about reading explain =
plans, and expand my use of different constructs.   It's so easy to fall =
into the trap of using the same construct in all situations just because =
it works well enough most of the time and is easy to read. =20

As for default_statistics_target, I read the docs and I'm not sure how =
increasing that value would help in this case.  There are only a couple =
hundred accounts, and less than 5 values for message_type and =
spam_level.  In the emsg_message_folders table, the message_id is =
considered unique (pg_stats has n_distinct =3D -1), which would also be =
correct.=20


EXPLAIN ANALYZE
SELECT ema.message_id, ema.email_address_id, ema.address_type
FROM emsg_message_addresses ema
WHERE ema.message_id IN (
	SELECT id=20
	FROM emsg_messages msg=20
	WHERE msg.account_id =3D 314 AND msg.outgoing =3D FALSE=20
	  AND msg.message_type =3D 1 AND msg.spam_level < 2=20
	  AND msg.deleted_at IS NULL =20
	  AND NOT EXISTS (
			SELECT emf.message_id=20
			FROM emsg_message_folders emf=20
			WHERE emf.account_id =3D 314 AND emf.message_id =
=3D msg.id
	)
)


QUERY PLAN=09
Nested Loop  (cost=3D84785.80..84806.43 rows=3D100455 width=3D12) =
(actual time=3D262.507..262.528 rows=3D6 loops=3D1)=09
  ->  HashAggregate  (cost=3D84785.80..84785.81 rows=3D1 width=3D4) =
(actual time=3D262.445..262.446 rows=3D3 loops=3D1)=09
        ->  Hash Anti Join  (cost=3D8285.87..84785.80 rows=3D1 width=3D4) =
(actual time=3D254.363..262.426 rows=3D3 loops=3D1)=09
              Hash Cond: (msg.id =3D emf.message_id)=09
              ->  Bitmap Heap Scan on emsg_messages msg  =
(cost=3D869.66..77274.56 rows=3D7602 width=3D4) (actual =
time=3D13.622..204.879 rows=3D12387 loops=3D1)=09
                    Recheck Cond: (account_id =3D 314)=09
                    Filter: ((NOT outgoing) AND (deleted_at IS NULL) AND =
(spam_level < 2) AND (message_type =3D 1))=09
                    ->  Bitmap Index Scan on =
index_emsg_messages_on_account_id  (cost=3D0.00..867.76 rows=3D34582 =
width=3D0) (actual time=3D8.756..8.756 rows=3D35091 loops=3D1)=09
                          Index Cond: (account_id =3D 314)=09
              ->  Hash  (cost=3D6990.69..6990.69 rows=3D34042 width=3D4) =
(actual time=3D45.785..45.785 rows=3D34647 loops=3D1)=09
                    Buckets: 4096  Batches: 1  Memory Usage: 1219kB=09
                    ->  Bitmap Heap Scan on emsg_message_folders emf  =
(cost=3D680.16..6990.69 rows=3D34042 width=3D4) (actual =
time=3D5.465..35.842 rows=3D34647 loops=3D1)=09
                          Recheck Cond: (account_id =3D 314)=09
                          ->  Bitmap Index Scan on =
index_emsg_message_folders_on_account_id  (cost=3D0.00..671.65 =
rows=3D34042 width=3D0) (actual time=3D4.966..4.966 rows=3D34647 =
loops=3D1)=09
                                Index Cond: (account_id =3D 314)=09
  ->  Index Scan using index_emsg_message_addresses_on_message_id on =
emsg_message_addresses ema  (cost=3D0.00..20.45 rows=3D13 width=3D12) =
(actual time=3D0.023..0.023 rows=3D2 loops=3D3)=09
        Index Cond: (ema.message_id =3D msg.id)=09
Total runtime: 262.742 ms=09


Jim Crate