Bill Kirtley <bill@actbluetech.com> writes:
> On the main production database, a select looking at the email column
> winds up scanning the whole table:
> ... where on that same database selecting on the 'key' column uses the
> index as expected:
That's just bizarre. I assume that setting enable_seqscan = off
doesn't persuade it to use the index either?
> Dropping and re-adding that 'index_users_on_email' had no effect.
How did you do that exactly? A regular CREATE INDEX, or did you
use CREATE INDEX CONCURRENTLY? If the latter, please show the output
from
select xmin,* from pg_index where indexrelid = 'index_users_on_email'::regclass;
I notice you have two indexes on email:
> Indexes:
> "users_pkey" PRIMARY KEY, btree (id)
> "index_users_on_email" UNIQUE, btree (email)
> "users_key_index" btree (key)
> "xxx" btree (email)
I can't think why that would be a problem, but does getting rid of
the "xxx" one make a difference?
> We have test databases which are restored (pg_dump/pg_restore) backups
> of this data, and on these the select on 'email' uses the index as
> expected.
Are the test machines using the exact same Postgres executables?
regards, tom lane