From: Mike Sofen <msofen@runbox.com>
To: pgsql-performance@postgresql.org
Subject: Re: Unexpected expensive index scan
Date: Tue, 27 Sep 2016 17:41:55 -0700
Message-ID: <00e901d21921$1e647180$5b2d5480$@runbox.com> (raw)
In-Reply-To: <CAP3LSG7PfEfaK5V6rT7rgP1MjLED-wnqGY5Qtp5Gx0s64L16tg@mail.gmail.com>
References: <CAP3LSG7iwYS6V555qqjNBvuoi3gsMhTU-nN7PcfxJhHhJVbXRw@mail.gmail.com>
<CAP3LSG7PfEfaK5V6rT7rgP1MjLED-wnqGY5Qtp5Gx0s64L16tg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
From: Jake Nielsen Sent: Tuesday, September 27, 2016 5:22 PM
the query
SELECT * FROM SyncerEvent WHERE ID > 12468 AND propogatorId NOT IN ('"d8130ab9!-66d0!-4f13!-acec!-a9556362f0ad"') AND conflicted != 1 AND userId = '57dc984f1c87461c0967e228' ORDER BY ID LIMIT 4000;^
On Tue, Sep 27, 2016 at 5:02 PM, Jake Nielsen <jake.k.nielsen@gmail.com <mailto:jake.k.nielsen@gmail.com> > wrote:
I've got a query that takes a surprisingly long time to run, and I'm having a really rough time trying to figure it out.
Before I get started, here are the specifics of the situation:
Here is the table that I'm working with (apologies for spammy indices, I've been throwing shit at the wall)
Table "public.syncerevent"
Column | Type | Modifiers
--------------+---------+----------------------------------------------------------
id | bigint | not null default nextval('syncerevent_id_seq'::regclass)
userid | text |
event | text |
eventid | text |
originatorid | text |
propogatorid | text |
kwargs | text |
conflicted | integer |
Indexes:
"syncerevent_pkey" PRIMARY KEY, btree (id)
"syncereventidindex" UNIQUE, btree (eventid)
"anothersyncereventidindex" btree (userid)
"anothersyncereventidindexwithascending" btree (userid, id)
"asdfasdgasdf" btree (userid, id DESC)
"syncereventuseridhashindex" hash (userid)
To provide some context, as per the wiki,
there are 3,290,600 rows in this table.
It gets added to frequently, but never deleted from.
The "kwargs" column often contains mid-size JSON strings (roughly 30K characters on average)
As of right now, the table has 53 users in it. About 20% of those have a negligible number of events, but the rest of the users have a fairly even smattering.
EXPLAIN (ANALYZE, BUFFERS) says:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..1218.57 rows=4000 width=615) (actual time=3352.390..3403.572 rows=4000 loops=1) Buffers: shared hit=120244 read=160198
-> Index Scan using syncerevent_pkey on syncerevent (cost=0.43..388147.29 rows=1274560 width=615) (actual time=3352.386..3383.100 rows=4000 loops=1)
Index Cond: (id > 12468)
Filter: ((propogatorid <> '"d8130ab9!-66d0!-4f13!-acec!-a9556362f0ad"'::text) AND (conflicted <> 1) AND (userid = '57dc984f1c87461c0967e228'::text))
Rows Removed by Filter: 1685801
Buffers: shared hit=120244 read=160198
Planning time: 0.833 ms
Execution time: 3407.633 ms
(9 rows)
If it matters/interests you, here is my underlying confusion:
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-performance@postgresql.org
Cc: msofen@runbox.com
Subject: Re: Unexpected expensive index scan
In-Reply-To: <00e901d21921$1e647180$5b2d5480$@runbox.com>
* 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