pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBetter performance possible for a pathological query?
3+ messages / 2 participants
[nested] [flat]
* Better performance possible for a pathological query?
@ 2013-08-07 15:38 Alexis Lê-Quôc <alq@datadoghq.com>
0 siblings, 1 reply; 3+ messages in thread
From: Alexis Lê-Quôc @ 2013-08-07 15:38 UTC (permalink / raw)
To: pgsql-performance
Hi,
I've seen a couple of bad queries go through one instance and I'm wondering
whether there's something simple that can be done to help.
Not running the query in the first place is what I am looking to do
ultimately but in the meantime, I'm interested in understanding more about
the plan below.
The query itself is very simple: a primary key lookup on a 1.5x10^7 rows.
The issue is that we are looking up over 11,000 primary keys at once,
causing the db to consume a lot of CPU.
Where I'm not sure I follow, is the discrepancy between the planned and
actual rows.
Bitmap Heap Scan on dim_context c (cost=6923.33..11762.31 rows=1
width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
Would a sequential scan be more beneficial? The table itself is about 10GB
on a 64GB box, 30% of these 10GB are buffered in RAM from what I can tell.
Thanks for your help,
Alexis
Here are the full details.
explain (analyze, buffers)
SELECT c.key,
c.x_id,
c.tags,
c.source_type_id,
x.api_key
FROM dim_context c
join x on c.x_id = x.id
WHERE c.key = ANY (ARRAY[15368196, (11,000 other keys)])
AND ((c.x_id = 1 AND c.tags @> ARRAY[E'blah']))
Here is the plan, abridged
Nested Loop (cost=6923.33..11770.59 rows=1 width=362) (actual
time=17128.188..22109.283 rows=10858 loops=1)
Buffers: shared hit=83494
-> Bitmap Heap Scan on dim_context c (cost=6923.33..11762.31 rows=1
width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_id = 1))
Filter: (key = ANY ('{15368196,(a lot more keys
here)}'::integer[]))
Buffers: shared hit=50919
-> BitmapAnd (cost=6923.33..6923.33 rows=269 width=0) (actual
time=132.910..132.910 rows=0 loops=1)
Buffers: shared hit=1342
-> Bitmap Index Scan on dim_context_tags_idx
(cost=0.00..1149.61 rows=15891 width=0) (actual time=64.614..64.614
rows=264777 loops=1)
Index Cond: (tags @> '{blah}'::text[])
Buffers: shared hit=401
-> Bitmap Index Scan on dim_context_x_id_source_type_id_idx
(cost=0.00..5773.47 rows=268667 width=0) (actual time=54.648..54.648
rows=267659 loops=1)
Index Cond: (x_id = 1)
Buffers: shared hit=941
-> Index Scan using x_pkey on x (cost=0.00..8.27 rows=1 width=37)
(actual time=0.003..0.004 rows=1 loops=10858)
Index Cond: (x.id = 1)
Buffers: shared hit=32575
Total runtime: 22117.417 ms
And here are the stats
attname | null_frac | avg_width | n_distinct | correlation
----------------+-----------+-----------+------------+-------------
key | 0 | 4 | -1 | 0.999558
x_id | 0 | 4 | 1498 | 0.351316
h_id | 0.05632 | 4 | 116570 | 0.653092
tags | 0.0544567 | 284 | 454877 | -0.169626
source_type_id | 0 | 4 | 23 | 0.39552
handle | 0 | 248 | -1 | 0.272456
created | 0 | 8 | -0.645231 | 0.999559
modified | 0 | 8 | -0.645231 | 0.999559
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Better performance possible for a pathological query?
@ 2013-08-07 16:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alexis Lê-Quôc <alq@datadoghq.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2013-08-07 16:07 UTC (permalink / raw)
To: Alexis Lê-Quôc <alq@datadoghq.com>; +Cc: pgsql-performance
=?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <alq@datadoghq.com> writes:
> The query itself is very simple: a primary key lookup on a 1.5x10^7 rows.
> The issue is that we are looking up over 11,000 primary keys at once,
> causing the db to consume a lot of CPU.
It looks like most of the runtime is probably going into checking the
c.key = ANY (ARRAY[...]) construct. PG isn't especially smart about that
if it fails to optimize the construct into an index operation --- I think
it's just searching the array linearly for each row meeting the other
restrictions on c.
You could try writing the test like this:
c.key = ANY (VALUES (1), (17), (42), ...)
to see if the sub-select code path gives better results than the array
code path. In a quick check it looked like this might produce a hash
join, which seemed promising anyway.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Better performance possible for a pathological query?
@ 2013-08-07 16:28 Alexis Lê-Quôc <alq@datadoghq.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Alexis Lê-Quôc @ 2013-08-07 16:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Wed, Aug 7, 2013 at 12:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?B?QWxleGlzIEzDqi1RdcO0Yw==?= <alq@datadoghq.com> writes:
> > The query itself is very simple: a primary key lookup on a 1.5x10^7 rows.
> > The issue is that we are looking up over 11,000 primary keys at once,
> > causing the db to consume a lot of CPU.
>
> It looks like most of the runtime is probably going into checking the
> c.key = ANY (ARRAY[...]) construct. PG isn't especially smart about that
> if it fails to optimize the construct into an index operation --- I think
> it's just searching the array linearly for each row meeting the other
> restrictions on c.
>
> You could try writing the test like this:
> c.key = ANY (VALUES (1), (17), (42), ...)
> to see if the sub-select code path gives better results than the array
> code path. In a quick check it looked like this might produce a hash
> join, which seemed promising anyway.
>
> regards, tom lane
>
Thank you very much Tom, your suggestion is spot on. Runtime decreased
100-fold, from 20s to 200ms with a simple search-and-replace.
Here's the updated plan for the record.
Nested Loop (cost=168.22..2116.29 rows=148 width=362) (actual
time=22.134..256.531 rows=10858 loops=1)
Buffers: shared hit=44967
-> Index Scan using x_pkey on x (cost=0.00..8.27 rows=1 width=37)
(actual time=0.071..0.073 rows=1 loops=1)
Index Cond: (id = 1)
Buffers: shared hit=4
-> Nested Loop (cost=168.22..2106.54 rows=148 width=329) (actual
time=22.060..242.406 rows=10858 loops=1)
Buffers: shared hit=44963
-> HashAggregate (cost=168.22..170.22 rows=200 width=4) (actual
time=21.529..32.820 rows=11215 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..140.19 rows=11215
width=4) (actual time=0.005..9.527 rows=11215 loops=1)
-> Index Scan using dim_context_pkey on dim_context c
(cost=0.00..9.67 rows=1 width=329) (actual time=0.015..0.016 rows=1
loops=11215)
Index Cond: (c.key = "*VALUES*".column1)
Filter: ((c.tags @> '{blah}'::text[]) AND (c.org_id = 1))
Buffers: shared hit=44963
Total runtime: 263.639 ms
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-08-07 16:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-07 15:38 Better performance possible for a pathological query? Alexis Lê-Quôc <alq@datadoghq.com>
2013-08-07 16:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-07 16:28 ` Alexis Lê-Quôc <alq@datadoghq.com>
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