Session opened on replica doesn't see concurrently created indexes at this time on master.
We have master and replica: 1. master: pgbench -i -s 10 2. replica: explain (analyze,verbose) select * from pgbench_accounts where abalance = 1; 3. master: ALTER INDEX pgbench_accounts_abalance_idx RENAME TO pgbench_accounts_abalance_idx_delme; CREATE INDEX CONCURRENTLY pgbench_accounts_abalance_idx ON pgbench_accounts USING btree (abalance); DROP INDEX pgbench_accounts_abalance_idx_delme; 4. at this time on replica: explain (analyze,verbose) select * from pgbench_accounts where abalance = 1; pgbench=# explain (analyze,verbose) select * from pgbench_accounts where abalance = 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Index Scan using pgbench_accounts_abalance_idx on public.pgbench_accounts (cost=0.42..4.44 rows=1 width=97) (actual time=655.781..655.781 rows=0 loops=1) Output: aid, bid, abalance, filler Index Cond: (pgbench_accounts.abalance = 1) Planning time: 9388.259 ms Execution time: 655.900 ms (5 rows) pgbench=# explain (analyze,verbose) select * from pgbench_accounts where abalance = 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using pgbench_accounts_abalance_idx_delme on public.pgbench_accounts (cost=0.42..4.44 rows=1 width=97) (actual time=0.014..0.014 rows=0 loops=1) Output: aid, bid, abalance, filler Index Cond: (pgbench_accounts.abalance = 1) Planning time: 0.321 ms Execution time: 0.049 ms (5 rows) pgbench=# explain (analyze,verbose) select * from pgbench_accounts where abalance = 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.pgbench_accounts (cost=0.00..28894.00 rows=1 width=97) (actual time=3060.451..3060.451 rows=0 loops=1) Output: aid, bid, abalance, filler Filter: (pgbench_accounts.abalance = 1) Rows Removed by Filter: 1000000 Planning time: 0.087 ms Execution time: 3060.484 ms (6 rows) pgbench=# \d+ pgbench_accounts Table "public.pgbench_accounts" Column | Type | Modifiers | Storage | Stats target | Description --------------------------------------------------+----------- aid | integer | not null | plain | | bid | integer | | plain | | abalance | integer | | plain | | filler | character(84) | | extended | | Indexes: "pgbench_accounts_pkey" PRIMARY KEY, btree (aid) "pgbench_accounts_abalance_idx" btree (abalance) Options: fillfactor=100 New opened session successfully uses this index. Tested with PostgreSQL 9.5.1. --- Dmitry Vasilyev Postgres Professional: http://www.postgrespro.com Russian Postgres Company
