Bill Kirtley <bill@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
