Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YFmaG-0002EL-W4 for pgsql-performance@arkaria.postgresql.org; Mon, 26 Jan 2015 16:32:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YFmaG-0007fz-Eg for pgsql-performance@arkaria.postgresql.org; Mon, 26 Jan 2015 16:32:16 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YFmaE-0007cO-FP for pgsql-performance@postgresql.org; Mon, 26 Jan 2015 16:32:14 +0000 Received: from mail1.bemta7.messagelabs.com ([216.82.254.101]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YFmaA-0003LZ-EJ for pgsql-performance@postgresql.org; Mon, 26 Jan 2015 16:32:12 +0000 Received: from [216.82.254.211] by server-5.bemta-7.messagelabs.com id 2B/41-03132-80C66C45; Mon, 26 Jan 2015 16:32:08 +0000 X-Env-Sender: Christian.Roche@workshare.com X-Msg-Ref: server-5.tower-201.messagelabs.com!1422289925!21299423!1 X-Originating-IP: [82.211.87.217] X-StarScan-Received: X-StarScan-Version: 6.12.5; banners=-,-,- X-VirusChecked: Checked Received: (qmail 29893 invoked from network); 26 Jan 2015 16:32:07 -0000 Received: from unknown (HELO ln1-ps-02.workshare.com) (82.211.87.217) by server-5.tower-201.messagelabs.com with AES128-SHA encrypted SMTP; 26 Jan 2015 16:32:07 -0000 Received: from LN1-EX2013-02.workshare.com ([10.10.0.202]) by ln1-ps-02.workshare.com over TLS secured channel with Microsoft SMTPSVC(7.5.7601.17514); Mon, 26 Jan 2015 16:32:43 +0000,from LN1-EX2013-01.workshare.com (10.10.0.201) by LN1-EX2013-02.workshare.com (10.10.0.202) with Microsoft SMTP Server (TLS) id 15.0.913.22; Mon, 26 Jan 2015 16:32:42 +0000,from LN1-EX2013-01.workshare.com ([fe80::c147:2831:e5db:962d]) by LN1-EX2013-01.workshare.com ([fe80::c147:2831:e5db:962d%14]) with mapi id 15.00.0913.011; Mon, 26 Jan 2015 16:32:23 +0000 From: "Christian Roche" To: "pgsql-performance@postgresql.org" Subject: Why is PostgreSQL not using my index? Thread-Topic: Why is PostgreSQL not using my index? Thread-Index: AdA5gfC/49LnzYbqRmi8+3j9322VHA== Date: Mon, 26 Jan 2015 16:32:22 +0000 Message-ID: Accept-Language: en-US, en-GB Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.10.3.109] MIME-Version: 1.0 X-OriginalArrivalTime: 26 Jan 2015 16:32:43.0757 (UTC) FILETIME=[B5D149D0:01D03985] Content-Type: multipart/alternative; boundary="--_=_NextPart0_4efbec9d-035e-48c5-8217-6b65be0150c7" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ----_=_NextPart0_4efbec9d-035e-48c5-8217-6b65be0150c7 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi guys, Can I take a jab at the celebrated =E2=80=9Cwhy is Postgres not using my in= dex=E2=80=9D riddle? I=E2=80=99m using PostgreSQL 9=2E3=2E3 on an Amazon RDS =E2=80=9Cdb=2Er3=2Ex= large=E2=80=9D 64-bit instance=2E I have two tables, one with about 30M row= s and two indexes (in fact a monthly partition): CREATE TABLE staging=2Emixpanel_events_201409 ( date_day date NOT NULL, event_id int NOT NULL REFERENCES mixpanel_event_list, prop hstore ); CREATE INDEX mixpanel_idx_date_201409 ON mixpanel_events_201409 USING btree (date_day); CREATE INDEX mixpanel_idx_event_201409 ON mixpanel_events_201409 USING btree (event_id); And a lookup table with about 600 rows: CREATE TABLE staging=2Emixpanel_event_list ( id serial PRIMARY KEY, name text UNIQUE, source event_source NULL ); Now when I select a subset of the possible event IDs in the big table, PG u= ses the appropriate index: select * from mixpanel_events_201409 where event_id in (3, 4, 5, 6, 7, 8, 9, 10, 11, 373, 375, 376, 318); Bitmap Heap Scan on mixpanel_events_201409 (cost=3D7663=2E36=2E=2E1102862= =2E70 rows=3D410022 width=3D949) Recheck Cond: (event_id =3D ANY ('{3,4,5,6,7,8,9,10,11,373,375,376,318}':= :integer[])) -> Bitmap Index Scan on mixpanel_idx_event_201409 (cost=3D0=2E00=2E=2E7= 560=2E85 rows=3D410022 width=3D0) Index Cond: (event_id =3D ANY ('{3,4,5,6,7,8,9,10,11,373,375,376,31= 8}'::integer[])) But when I try to join the lookup table and select from it, the index is di= smissed for a full table scan with a catastrophic effect on performance: select * from mixpanel_events_201409 mp inner join mixpanel_event_list ev on ( ev=2Eid =3D mp=2Eevent_id ) where ev=2Eid in (3, 4, 5, 6, 7, 8, 9, 10, 11, 373, 375, 376, 318); Hash Join (cost=3D20=2E73=2E=2E2892183=2E32 rows=3D487288 width=3D1000) Hash Cond: (mp=2Eevent_id =3D ev=2Eid) -> Seq Scan on mixpanel_events_201409 mp (cost=3D0=2E00=2E=2E2809276=2E7= 0 rows=3D20803470 width=3D949) -> Hash (cost=3D20=2E57=2E=2E20=2E57 rows=3D13 width=3D51) -> Seq Scan on mixpanel_event_list ev (cost=3D0=2E00=2E=2E20=2E57= rows=3D13 width=3D51) Filter: (id =3D ANY ('{3,4,5,6,7,8,9,10,11,373,375,376,318}':= :integer[])) Both tables have been vacuum analyzed=2E What gives? Thanks a lot for your help, Chris This email is from Workshare Limited=2E The information contained in and ac= companying this communication may be confidential, subject to legal privile= ge, or otherwise protected from disclosure, and is intended solely for the = use of the intended recipient(s)=2E If you are not the intended recipient o= f this communication, please delete and destroy all copies in your possessi= on and note that any review or dissemination of, or the taking of any actio= n in reliance on, this communication is expressly prohibited=2E Please cont= act the sender if you believe you have received this email in error=2E Work= share Limited is a limited liability company registered in England and Wale= s (registered number 3559880), its registered office is at 20 Fashion Stree= t, London, E1 6PX for further information, please refer to http://www=2Ewor= kshare=2Ecom=2E ----_=_NextPart0_4efbec9d-035e-48c5-8217-6b65be0150c7 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

= Hi guys,

=  

= Can I take a jab at the celebrated =E2=80=9Cwhy is Postgres not using my in= dex=E2=80=9D riddle?

=  

= I=E2=80=99m using PostgreSQL 9=2E3=2E3 on an Amazon RDS =E2=80=9Cdb=2Er3=2Ex= large=E2=80=9D 64-bit instance=2E I have two tables, one with about 30M row= s and two indexes (in fact a monthly partition):

=  

= CREATE TABLE staging=2Emixpanel_events_201409 (

=   date_day          date = NOT NULL,

=   event_id          int N= OT NULL REFERENCES mixpanel_event_list,

=   prop          &nbs= p;   hstore

= );

=  

= CREATE INDEX mixpanel_idx_date_201409

=   ON mixpanel_events_201409

=   USING btree

=   (date_day);

=  

= CREATE INDEX mixpanel_idx_event_201409

=   ON mixpanel_events_201409

=   USING btree

=   (event_id);

=  

=  

= And a lookup table with about 600 rows:

=  

= CREATE TABLE staging=2Emixpanel_event_list (

=   id           =      serial PRIMARY KEY,

=   name          &nbs= p;   text UNIQUE,

=   source          &n= bsp; event_source NULL

= );

=  

=  

= Now when I select a subset of the possible event IDs in the big table, PG u= ses the appropriate index:

=  

= select *

=   from mixpanel_events_201409

=  where event_id in (3, 4, 5, 6, 7, 8, 9, 10, 11, 373, 375, 376, 318);<= o:p>

=  

=  

= Bitmap Heap Scan on mixpanel_events_201409  (cost=3D7663=2E36=2E=2E110= 2862=2E70 rows=3D410022 width=3D949)

=   Recheck Cond: (event_id =3D ANY ('{3,4,5,6,7,8,9,10,11,373,375,376,3= 18}'::integer[]))

=   ->  Bitmap Index Scan on mixpanel_idx_event_201409  (co= st=3D0=2E00=2E=2E7560=2E85 rows=3D410022 width=3D0)

=         Index Cond: (event_id =3D ANY ('= {3,4,5,6,7,8,9,10,11,373,375,376,318}'::integer[]))

=  

=  

= But when I try to join the lookup table and select from it, the index is di= smissed for a full table scan with a catastrophic effect on performance:

=  

= select *

= from mixpanel_events_201409 mp

=   inner join mixpanel_event_list ev on ( ev=2Eid =3D mp=2Eevent_i= d )

= where ev=2Eid in (3, 4, 5, 6, 7, 8, 9, 10, 11, 373, 375, 376, 318);

=  

= Hash Join  (cost=3D20=2E73=2E=2E2892183=2E32 rows=3D487288 width=3D100= 0)

=   Hash Cond: (mp=2Eevent_id =3D ev=2Eid)

=   ->  Seq Scan on mixpanel_events_201409 mp  (cost=3D0=2E0= 0=2E=2E2809276=2E70 rows=3D20803470 width=3D949)

=   ->  Hash  (cost=3D20=2E57=2E=2E20=2E57 rows=3D13 width= =3D51)

=         ->  Seq Scan on mixpanel= _event_list ev  (cost=3D0=2E00=2E=2E20=2E57 rows=3D13 width=3D51)=

=             &nb= sp; Filter: (id =3D ANY ('{3,4,5,6,7,8,9,10,11,373,375,376,318}'::integer[]= ))

=  

=  

= Both tables have been vacuum analyzed=2E

=  

= What gives?

=  

= Thanks a lot for your help,

Chris

=  

This email is from Workshare Limited=2E The inf= ormation contained in and accompanying this communication may be confidenti= al, subject to legal privilege, or otherwise protected from disclosure, and= is intended solely for the use of the intended recipient(s)=2E If you are not the intended recipient of this com= munication, please delete and destroy all copies in your possession and not= e that any review or dissemination of, or the taking of any action in relia= nce on, this communication is expressly prohibited=2E Please contact the sender if you believe you have received t= his email in error=2E Workshare Limited is a limited liability company regi= stered in England and Wales (registered number 3559880), its registered off= ice is at 20 Fashion Street, London, E1 6PX for further information, please refer to http://www=2Eworkshare=2Ec= om=2E

----_=_NextPart0_4efbec9d-035e-48c5-8217-6b65be0150c7--