Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id E28D71FBF225 for ; Tue, 27 Dec 2011 13:29:33 -0400 (AST) Received: from mail-yw0-f46.google.com ([209.85.213.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Rfaq9-0003li-RL for pgsql-performance@postgresql.org; Tue, 27 Dec 2011 17:29:34 +0000 Received: by yhr47 with SMTP id 47so7535318yhr.19 for ; Tue, 27 Dec 2011 09:29:17 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=from:content-type:content-transfer-encoding:subject:date:message-id :to:mime-version:x-mailer; bh=x17endfgiqFd8Mphl/pb5c2T/Q6CfAL6UUaw8lrJsXU=; b=eejcFFVQBYB3Ov4QF4GJmLL36vky01S59gpMIrSsuzES+YpMa99810NYlTUZIDVkih mKI65A6srPUa6EJS9uGS1M4HE0LATfxQfcmpYD9++Y3DANHj8l0b1hFZz8Hr5GK+5ewu zyVite0CC8rzdY+IeMolrYewoXodg9Uoh8SSU= Received: by 10.236.200.164 with SMTP id z24mr38517366yhn.127.1325006957361; Tue, 27 Dec 2011 09:29:17 -0800 (PST) Received: from [192.168.1.126] (99-14-184-104.lightspeed.wepbfl.sbcglobal.net. [99.14.184.104]) by mx.google.com with ESMTPS id o7sm40520761yhl.15.2011.12.27.09.29.15 (version=TLSv1/SSLv3 cipher=OTHER); Tue, 27 Dec 2011 09:29:16 -0800 (PST) From: Jim Crate Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Subject: Subquery flattening causing sequential scan Date: Tue, 27 Dec 2011 12:29:14 -0500 Message-Id: <0E417F76-FE21-4FB5-8132-0C485778A041@gmail.com> To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Apple Message framework v1084) X-Mailer: Apple Mail (2.1084) X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201112/180 X-Sequence-Number: 45979 PostgreSQL 9.0.2 Mac OS X Server 10.6.8 Autovacuum is on, and I have a script that runs vacuum analyze verbose = every night along with the backup. I have a situation where I'm experiencing a seq scan on a table with = almost 3M rows when my condition is based on a subquery. A google = search turned up a way to prevent flattening the subquery into a join = using OFFSET 0. This does work, reducing the query from around 1s to = around 250ms, most of which is the subquery. =20 My question is why does it do a seq scan when it flattens this subquery = into a JOIN? Is it because the emsg_messages table is around 1M rows? = Are there some guidelines to when the planner will prefer not to use an = available index? I just had a look through postgresql.conf and noticed = that I forgot to set effective_cache_size to something reasonable for a = machine with 16GB of memory. Would the default setting of 128MB cause = this behavior? I can't bounce the production server midday to test that = change. 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 msg.id NOT IN ( SELECT emf.message_id=20 FROM emsg_message_folders emf=20 where emf.account_id =3D 314 ) ) QUERY PLAN=09 Hash Semi Join (cost=3D84522.74..147516.35 rows=3D49545 width=3D12) = (actual time=3D677.058..1083.685 rows=3D2 loops=3D1)=09 Hash Cond: (ema.message_id =3D msg.id)=09 -> Seq Scan on emsg_message_addresses ema (cost=3D0.00..53654.78 = rows=3D2873478 width=3D12) (actual time=3D0.020..424.241 rows=3D2875437 = loops=3D1)=09 -> Hash (cost=3D84475.45..84475.45 rows=3D3783 width=3D4) (actual = time=3D273.392..273.392 rows=3D1 loops=3D1)=09 Buckets: 1024 Batches: 1 Memory Usage: 1kB=09 -> Bitmap Heap Scan on emsg_messages msg = (cost=3D7979.35..84475.45 rows=3D3783 width=3D4) (actual = time=3D273.224..273.387 rows=3D1 loops=3D1)=09 Recheck Cond: (account_id =3D 314)=09 Filter: ((NOT outgoing) AND (deleted_at IS NULL) AND = (spam_level < 2) AND (NOT (hashed SubPlan 1)) AND (message_type =3D 1))=09= -> Bitmap Index Scan on index_emsg_messages_on_account_id = (cost=3D0.00..867.98 rows=3D34611 width=3D0) (actual time=3D9.633..9.633 = rows=3D34997 loops=3D1)=09 Index Cond: (account_id =3D 314)=09 SubPlan 1=09 -> Bitmap Heap Scan on emsg_message_folders emf = (cost=3D704.90..7022.51 rows=3D35169 width=3D4) (actual = time=3D5.684..38.016 rows=3D34594 loops=3D1)=09 Recheck Cond: (account_id =3D 314)=09 -> Bitmap Index Scan on = index_emsg_message_folders_on_account_id (cost=3D0.00..696.10 = rows=3D35169 width=3D0) (actual time=3D5.175..5.175 rows=3D34594 = loops=3D1)=09 Index Cond: (account_id =3D 314)=09 Total runtime: 1083.890 ms=09 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 msg.id NOT IN ( SELECT emf.message_id=20 FROM emsg_message_folders emf=20 where emf.account_id =3D 314 ) OFFSET 0 ) QUERY PLAN=09 Nested Loop (cost=3D84524.89..87496.74 rows=3D2619 width=3D12) (actual = time=3D273.409..273.412 rows=3D2 loops=3D1)=09 -> HashAggregate (cost=3D84524.89..84526.89 rows=3D200 width=3D4) = (actual time=3D273.345..273.346 rows=3D1 loops=3D1)=09 -> Limit (cost=3D7979.36..84477.60 rows=3D3783 width=3D4) = (actual time=3D273.171..273.335 rows=3D1 loops=3D1)=09 -> Bitmap Heap Scan on emsg_messages msg = (cost=3D7979.36..84477.60 rows=3D3783 width=3D4) (actual = time=3D273.169..273.333 rows=3D1 loops=3D1)=09 Recheck Cond: (account_id =3D 314)=09 Filter: ((NOT outgoing) AND (deleted_at IS NULL) AND = (spam_level < 2) AND (NOT (hashed SubPlan 1)) AND (message_type =3D 1))=09= -> Bitmap Index Scan on = index_emsg_messages_on_account_id (cost=3D0.00..867.99 rows=3D34612 = width=3D0) (actual time=3D9.693..9.693 rows=3D34998 loops=3D1)=09 Index Cond: (account_id =3D 314)=09 SubPlan 1=09 -> Bitmap Heap Scan on emsg_message_folders emf = (cost=3D704.90..7022.51 rows=3D35169 width=3D4) (actual = time=3D5.795..39.420 rows=3D34594 loops=3D1)=09 Recheck Cond: (account_id =3D 314)=09 -> Bitmap Index Scan on = index_emsg_message_folders_on_account_id (cost=3D0.00..696.10 = rows=3D35169 width=3D0) (actual time=3D5.266..5.266 rows=3D34594 = 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..14.69 rows=3D13 width=3D12) = (actual time=3D0.056..0.058 rows=3D2 loops=3D1)=09 Index Cond: (ema.message_id =3D msg.id)=09 Total runtime: 273.679 ms=09 Jim Crate