Re: Use of sequence rather than index scan for one text column on one instance of a database

Bill Kirtley <bill(at)actbluetech(dot)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