In our PostgreSQL 9.4.4 database we have a table that receives around 600k new records each day. Each day, nightly, we are performing some ETL exports from the table. If it has not been analyzed before the export, it is really slow. If we run ANALYZE
, much faster, because the planner uses multicolumn index that has the field we query for. What would be the preferred way to solve the slow query issue? I see three options:
ANALYZE
right before the export,- use automatic vacuum/analyze feature,
- add query specific index.
The second option requires us to specify the auto vacuuming/analyzing settings per table, since the defaults are not working properly for big tables. And even if we do set it properly, there are some edge cases, when it still can cause troubles. For example:
autovacuum_analyze_scale_factor = 0.1
is the default now - so if our table is ~25 million records, it will ANALYZE after 2.5 millions, which is not frequent enough for us (we have ~600k new transactions every day). But if we set it to 0.02 (~500k records) it may happen, that for a day with many transactions (900k for example) we will have run ANALYZE after 500k but 400k will remain unanalysed, which would impact the query performance.
The table structure:
Table "public.bet_transactions"
Column | Type | Modifiers
----------------------------+-----------------------------+---------------------------------------------------------------
id | integer | not null default nextval('bet_transactions_id_seq'::regclass)
account_id | integer | not null
amount_cents | integer | not null default 0
money_amount_cents | integer | not null default 0
bonus_amount_cents | integer | not null default 0
wager_amount_cents | integer | not null default 0
currency | character varying(3) | not null default 'EUR'::character varying
total_balance_before_cents | integer | not null default 0
total_balance_after_cents | integer | not null default 0
money_balance_before_cents | integer | not null default 0
money_balance_after_cents | integer | not null default 0
bonus_balance_before_cents | integer | not null default 0
bonus_balance_after_cents | integer | not null default 0
wager_balance_before_cents | integer | not null default 0
wager_balance_after_cents | integer | not null default 0
status | character varying(255) | not null
reason | character varying(255) |
provider | character varying(255) | not null
external_unique_id | character varying(255) |
external_group_id | character varying(255) |
external_reason | character varying(255) |
external_data | hstore | not null default ''::hstore
search_vector | tsvector |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
exchange_rate | double precision | not null default 1
original_currency | character varying(3) | not null default 'EUR'::character varying
original_amount_cents | integer | not null default 0
game_id | integer | not null
external_game_id | character varying(255) |
big_win | boolean |
contribution_factor | integer |
Indexes:
"bet_transactions_pkey" PRIMARY KEY, btree (id)
"ux_bet_transactions_provider_external_unique_id" UNIQUE, btree (account_id, provider, external_unique_id) WHERE external_unique_id IS NOT NULL
"ix_bet_transactions_account_game_status_and_date" btree (account_id, game_id, created_at) WHERE status::text = 'accepted'::text AND created_at >= '2015-01-01 00:00:00'::timestamp without time zone
"ix_bet_transactions_account_id_external_group_id" btree (account_id, external_group_id)
"ix_bet_transactions_date_created_at" btree (date(created_at))
Inherits: game_transactions
Sample query (batchified):
SELECT "bet_transactions".*, "bet_transactions".tableoid::regclass::text as "table_name" FROM "bet_transactions" INNER JOIN "accounts" ON "accounts"."id" = "bet_transactions"."account_id" INNER JOIN "players" ON "players"."id" = "accounts"."player_id" WHERE (bet_transactions.tableoid::regclass::text = 'bet_transactions'::text) AND ("bet_transactions"."created_at" >= '2015-07-02 22:00:00.000000' AND "bet_transactions"."created_at" < '2015-07-03 22:00:00.000000') AND "bet_transactions"."status" = 'accepted' ORDER BY "bet_transactions"."created_at" ASC LIMIT 5000 OFFSET 0
Do you see any alternatives? How is such situation usually handled?
EDIT: Added the table structure and a sample query.
VACUUM
part of autovacuum, regardless.) What other (important) queries on the same table and when? If there are no other queries that might profit fromANALYZE
, why not run it just before the big query?