pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bad Index Choices with user defined data type
2+ messages / 2 participants
[nested] [flat]

* Bad Index Choices with user defined data type
@ 2005-01-03 21:44  Adam Palmblad <adam@aers.ca>
  0 siblings, 1 reply; 2+ messages in thread

From: Adam Palmblad @ 2005-01-03 21:44 UTC (permalink / raw)
  To: pgsql-performance

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Bad Index Choices with user defined data type
@ 2005-01-04 05:06  Michael Fuhr <mike@fuhr.org>
  parent: Adam Palmblad <adam@aers.ca>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Fuhr @ 2005-01-04 05:06 UTC (permalink / raw)
  To: Adam Palmblad <adam@aers.ca>; +Cc: pgsql-performance

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/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-01-04 05:06 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-03 21:44 Bad Index Choices with user defined data type Adam Palmblad <adam@aers.ca>
2005-01-04 05:06 ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox