pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBad plan choices & statistic targets with a GIN index
2+ messages / 2 participants
[nested] [flat]
* Bad plan choices & statistic targets with a GIN index
@ 2013-11-14 14:36 Dieter Komendera <dieter@komendera.com>
2013-11-14 15:00 ` Re: Bad plan choices & statistic targets with a GIN index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Dieter Komendera @ 2013-11-14 14:36 UTC (permalink / raw)
To: pgsql-performance
Hi all,
hope this is the right list to post to.
We saw some bad choices from the query planner regarding the use of a GIN index which got worse over time and performance started degrading seriously, so I did some digging and I found a solution which works, but I'd like to get some opinion on.
Here is the table in question:
Table "public.games"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+-----------------------------+----------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('games_id_seq'::regclass) | plain | |
runners | smallint | | plain | |
player_id | integer | | plain | 1000 |
partner1_id | integer | | plain | 1000 |
partner2_id | integer | | plain | 1000 |
partner3_id | integer | | plain | 1000 |
created_at | timestamp without time zone | | plain | |
Indexes:
"games_pkey" PRIMARY KEY, btree (id)
"index_games_on_created_at" btree (created_at)
"index_games_participants" gin ((ARRAY[player_id, partner1_id, partner2_id, partner3_id])) WITH (fastupdate=off)
Has OIDs: no
I removed some columns from the output for clarity,. It has 300+ million rows. And is freshly analyzed.
As you see, I've already increased the stats targets for the columns which go into the GIN index before, but this had no visible effect on query plan choices.
Here's a typical query:
EXPLAIN (analyze, buffers) SELECT "games".* FROM "games" WHERE (ABS(runners) >= '3') AND ((ARRAY[player_id, partner1_id, partner2_id, partner3_id]) @> ARRAY[166866]) ORDER BY id DESC LIMIT 20 OFFSET 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.57..13639.64 rows=20 width=74) (actual time=330.271..12372.777 rows=20 loops=1)
Buffers: shared hit=3453594 read=119394
-> Index Scan Backward using games_pkey on games (cost=0.57..15526034.64 rows=22767 width=74) (actual time=330.269..12372.763 rows=20 loops=1)
Filter: ((ARRAY[player_id, partner1_id, partner2_id, partner3_id] @> '{166866}'::integer[]) AND (abs(runners) >= 3::smallint))
Rows Removed by Filter: 3687711
Buffers: shared hit=3453594 read=119394
Total runtime: 12372.848 ms
(7 rows)
This is plan is not the best choice, though. It would be much more efficient to use the index_games_participants index. For some queries, there would be not enough records which fullfill the conditions so bascially every row of the table is scanned.
As \d+ index_games_participants showed that the index had an "array" column, I found this:
SELECT attname, attstattarget from pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'index_games_participants');
attname | attstattarget
---------+---------------
array | -1
(1 row)
Also, I noticed that for that "array" GIN index column there is content in pg_statistics, where as for the btree indices there isn't.
Because I didn't find any documentation or references on setting statistic targets on indices, I just gave it a shot:
ALTER TABLE index_games_participants ALTER COLUMN "array" SET STATISTICS 1000;
After running ANALYZE on the table:
EXPLAIN (analyze, buffers) SELECT "games".* FROM "games" WHERE (ABS(runners) >= '3') AND ((ARRAY[player_id, partner1_id, partner2_id, partner3_id]) @> ARRAY[166866]) ORDER BY id DESC LIMIT 20 OFFSET 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=33947.27..33947.32 rows=20 width=74) (actual time=624.308..624.341 rows=20 loops=1)
Buffers: shared hit=4 read=17421
-> Sort (cost=33947.27..33961.61 rows=5736 width=74) (actual time=624.306..624.318 rows=20 loops=1)
Sort Key: id
Sort Method: top-N heapsort Memory: 27kB
Buffers: shared hit=4 read=17421
-> Bitmap Heap Scan on games (cost=164.49..33794.64 rows=5736 width=74) (actual time=6.704..621.592 rows=1963 loops=1)
Recheck Cond: (ARRAY[player_id, partner1_id, partner2_id, partner3_id] @> '{166866}'::integer[])
Filter: (abs(runners) >= 3::smallint)
Rows Removed by Filter: 17043
Buffers: shared hit=1 read=17421
-> Bitmap Index Scan on index_games_participants (cost=0.00..163.05 rows=17207 width=0) (actual time=4.012..4.012 rows=19300 loops=1)
Index Cond: (ARRAY[player_id, partner1_id, partner2_id, partner3_id] @> '{166866}'::integer[])
Buffers: shared hit=1 read=19
Total runtime: 624.572 ms
(15 rows)
Much better! This reduced the bad plan choices substantially.
Also, as one could expect, SELECT * from pg_statistic WHERE starelid = (SELECT oid FROM pg_class WHERE relname = 'index_games_participants'); now had much more data.
Is this a good idea? Am I missing something? Or should the GIN index actually use the statistic targets derived from the table columns it depends on?
Best,
Dieter
--
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] 2+ messages in thread
* Re: Bad plan choices & statistic targets with a GIN index
2013-11-14 14:36 Bad plan choices & statistic targets with a GIN index Dieter Komendera <dieter@komendera.com>
@ 2013-11-14 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2013-11-14 15:00 UTC (permalink / raw)
To: Dieter Komendera <dieter@komendera.com>; +Cc: pgsql-performance
Dieter Komendera <dieter@komendera.com> writes:
> Because I didn't find any documentation or references on setting statistic targets on indices, I just gave it a shot:
> ALTER TABLE index_games_participants ALTER COLUMN "array" SET STATISTICS 1000;
This works, and will help if the planner can make use of statistics on the
expression the index is indexing. The reason it's not documented is that
it's not considered supported (yet), primarily because pg_dump won't dump
such a setting. And the reason for that is mainly that the column names
of an index aren't guaranteed stable across PG versions. But as long
as you're willing to remember to restore the setting manually, it's
a reasonable thing to do if it helps your query plans.
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] 2+ messages in thread
end of thread, other threads:[~2013-11-14 15:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-11-14 14:36 Bad plan choices & statistic targets with a GIN index Dieter Komendera <dieter@komendera.com>
2013-11-14 15:00 ` Tom Lane <tgl@sss.pgh.pa.us>
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