Thread: Bad Index Choices with user defined data type
I've got a table using a data type that I have created as the type for its primary key. I (hope) I have the type set up properly - it seems okay, and does not have any problem creating a b-tree index for the type. The problem I am having is that the index seems to never be chosen for use. I can force the use of the index by setting enable_seqscan to off. The table has about 1.2 million rows. I have also analyzed the table - and immediately afterwards there is no affect on the index's behaviour.
Any thoughts?
-Adam
On Mon, Jan 03, 2005 at 01:44:27PM -0800, Adam Palmblad wrote: > I've got a table using a data type that I have created as the type for > its primary key. I (hope) I have the type set up properly - it seems > okay, and does not have any problem creating a b-tree index for the > type. The problem I am having is that the index seems to never be > chosen for use. I can force the use of the index by setting > enable_seqscan to off. The table has about 1.2 million rows. I have > also analyzed the table - and immediately afterwards there is no affect > on the index's behaviour. Please post the query and the EXPLAIN ANALYZE output for both cases: one query with enable_seqscan on and one with it off. It might also be useful to see the column's statistics from pg_stats, and perhaps the SQL statements that create the table, the type, the type's operators, etc. -- Michael Fuhr http://www.fuhr.org/~mfuhr/