MIME-Version: 1.0
Subject: BUG #11500: PRIMARY KEY index not being used
To: pgsql-bugs@postgresql.org
From: marko@joh.to
Date: Fri, 26 Sep 2014 08:02:11 +0000
Message-ID: <20140926080211.7639.68358@wrigleys.postgresql.org>
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

The following bug has been logged on the website:

Bug reference:      11500
Logged by:          Marko Tiikkaja
Email address:      marko@joh.to
PostgreSQL version: 9.1.12
Operating system:   Linux
Description:        

Hi,

We've been observing a performance problem where a PRIMARY KEY index is not
being used.  The problem looks like this:

pg2=#* explain analyze select * from events where eventid = 132685185 and
processed = 0;
                                                            QUERY PLAN      
                                                      
-----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_events_processed on events  (cost=0.00..7.73 rows=1
width=106) (actual time=31.808..31.808 rows=0 loops=1)
   Index Cond: (processed = 0)
   Filter: (eventid = 132685185)
 Total runtime: 31.852 ms
(4 rows)

pg2=#* explain analyze select * from events where eventid = 132685185 and
processed+0 = 0;
                                                      QUERY PLAN            
                                          
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using events_pkey on events  (cost=0.00..12.38 rows=1 width=106)
(actual time=0.071..0.071 rows=0 loops=1)
   Index Cond: (eventid = 132685185)
   Filter: ((processed + 0) = 0)
 Total runtime: 0.109 ms
(4 rows)


I'm guessing that's happening because the index on processed is smaller
(7GB, relpages=900880 vs 3.7GB, relpages=478225).  The statistics say that
there are no rows where processed=0 (and it's not far from the truth), but
it's still a risky plan compared to the PK lookup.  The index
index_events_processed is an index on events(processed), which should
probably be a partial index on  WHERE processed = 0, but I thought I'd
report this plan anyway.

Any thoughts?


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs