From: marko@joh.to
To: pgsql-bugs@postgresql.org
Subject: BUG #11500: PRIMARY KEY index not being used
Date: Fri, 26 Sep 2014 08:02:11 +0000
Message-ID: <20140926080211.7639.68358@wrigleys.postgresql.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: marko@joh.to
Subject: Re: BUG #11500: PRIMARY KEY index not being used
In-Reply-To: <20140926080211.7639.68358@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox