From: Greg Stark <gsstark@mit.edu>
To: Patrick Clery <patrick@phpforhire.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparing user attributes with bitwise operators
Date: 06 Oct 2004 15:38:56 -0400
Message-ID: <87y8ijy6un.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200410061255.03052.patrick@phpforhire.com>
References: <200409160141.37604.patrick@phpforhire.com>
<200410050039.24230.patrick@phpforhire.com>
<200410050932.38595.josh@agliodbs.com>
<200410061255.03052.patrick@phpforhire.com>
Patrick Clery <patrick@phpforhire.com> writes:
> PART OF THE QUERY PLAN:
> Index Scan using people_attributes_pkey on people_attributes pa (cost=0.00..5.32 rows=1 width=20)
> Index Cond: (pa.person_id = "outer".person_id)
> Filter: (((ARRAY[age, gender, orientation, children, drinking,
You'll probably have to show the rest of the plan for anyone to have much idea
what's going on. It seems to be part of a join of some sort and the planner is
choosing to drive the join from the wrong table. This may make it awkward to
force the right plan using enable_seqscan or anything like that. But GiST
indexes don't have very good selectivity estimates so I'm not sure you can
hope for the optimizer to guess right on its own.
> Is it all the foreign keys that are stalling the drop? I have done VACUUM
> ANALYZE on the entire db. Could anyone offer some insight as to why this
> index is not being used or why the index is not dropping easily?
I don't think foreign keys cause problems dropping indexes. Foreign key
constraints are just checked whenever there's an insert/update/delete. Perhaps
you're just underestimating the size of this index and the amount of time
it'll take to delete it? Or are there queries actively executing using the
index while you're trying to delete it? Or a vacuum running?
--
greg
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, patrick@phpforhire.com
Subject: Re: Comparing user attributes with bitwise operators
In-Reply-To: <87y8ijy6un.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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