The ANALYZE command collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog.
These statistics help the query planner to determine the most efficient execution plans for queries..
Example:
console.log( 'Code is Poetry' );
Maybe that’s not the best plan, and we want to revert to the original plan.
To do that we’ll need to delete the statistics.
yugabyte=# SELECT COUNT(*) FROM pg_statistic WHERE starelid IN ('a'::regclass, 'b'::regclass);
count
-------
5
(1 row)
yugabyte=# DELETE FROM pg_statistic WHERE starelid IN ('a'::regclass, 'b'::regclass);
ERROR: Transaction for catalog table write operation 'pg_statistic' not found
That didn’t work… SYSTEM tables are protected.
But we can override this behaviour with the
yb_non_ddl_txn_for_sys_tables_allowed setting.
yugabyte=# SET yb_non_ddl_txn_for_sys_tables_allowed = on;
SET
yugabyte=# DELETE FROM pg_statistic WHERE starelid IN ('a'::regclass, 'b'::regclass);
DELETE 5
Now We’re back to the orginal plan:
yugabyte=# CREATE TABLE a (id serial PRIMARY KEY, deleted_at TIMESTAMP);
CREATE TABLE
yugabyte=# CREATE TABLE b (id serial PRIMARY KEY, id_a INTEGER, value INTEGER);
CREATE TABLE
yugabyte=# CREATE INDEX b_value ON b ( value , id_a );
CREATE INDEX
yugabyte=# INSERT INTO a(deleted_at) SELECT NULL FROM generate_series(1,10) i;
INSERT 0 10
yugabyte=# INSERT INTO b(value,id_a) SELECT i, a.id from generate_series(1,10) i, a;
INSERT 0 100
yugabyte=# EXPLAIN SELECT DISTINCT * FROM a WHERE deleted_at IS NULL
yugabyte-# AND id = ANY( SELECT id_a FROM b WHERE value = any (ARRAY[2]::int[]) );
QUERY PLAN
---------------------------------------------------------------------------------------------------
HashAggregate (cost=23.80..24.80 rows=100 width=12)
Group Key: a.id, a.deleted_at
-> Nested Loop (cost=12.65..23.30 rows=100 width=12)
-> HashAggregate (cost=12.65..13.47 rows=82 width=4)
Group Key: b.id_a
-> Distinct Index Only Scan using b_value on b (cost=0.00..12.40 rows=82 width=4)
Index Cond: (value = ANY ('{2}'::integer[]))
Distinct Prefix: 2
-> Index Scan using a_pkey on a (cost=0.00..0.13 rows=1 width=12)
Index Cond: (id = b.id_a)
Remote Filter: (deleted_at IS NULL)
(11 rows)
yugabyte=# ANALYZE a, b;
ANALYZE
yugabyte=# EXPLAIN SELECT * FROM a WHERE deleted_at IS NULL AND id = any( SELECT id_a FROM b WHERE value = any (ARRAY[2]::int[]) );
QUERY PLAN
----------------------------------------------------------------------------
Nested Loop Semi Join (cost=0.00..6.21 rows=10 width=12)
-> Seq Scan on a (cost=0.00..1.00 rows=10 width=12)
Remote Filter: (deleted_at IS NULL)
-> Index Only Scan using b_value on b (cost=0.00..0.51 rows=1 width=4)
Index Cond: ((value = ANY ('{2}'::integer[])) AND (id_a = a.id))
(5 rows)