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
