[BUGS] BUG #1681: Selection problem

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[BUGS] BUG #1681: Selection problem

fremaux

The following bug has been logged online:

Bug reference:      1681
Logged by:          fremaux
Email address:      [hidden email]
PostgreSQL version: 7.2.2
Operating system:   Linux kernel 2.4.6.19-mdk
Description:        Selection problem
Details:

following query :
SELECT list.group_artifact_id, list.name
FROM artifact_group_list list
WHERE (list.group_artifact_id=167)

returns 2 lines where the first list.group_artifact_id equals "165". The
problem is that the only lines that have to be returned are the ones with
list.group_artifact_id=167

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1681: Selection problem

Michael Fuhr
On Thu, May 26, 2005 at 02:50:44PM +0100, fremaux wrote:
>
> PostgreSQL version: 7.2.2

PostgreSQL 7.2.2 is almost three years old -- consider upgrading
to 7.2.8, or better yet, 8.0.3.

> SELECT list.group_artifact_id, list.name
> FROM artifact_group_list list
> WHERE (list.group_artifact_id=167)
>
> returns 2 lines where the first list.group_artifact_id equals "165". The
> problem is that the only lines that have to be returned are the ones with
> list.group_artifact_id=167

Could you post the query results and the query's EXPLAIN ANALYZE
output?  Do you have an index on group_artifact_id?  If so, and if
EXPLAIN ANALYZE shows that the query uses the index, what happens
if you set enable_indexscan to off?  What type is group_artifact_id?
How often do you vacuum, analyze, and/or reindex the table?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1681: Selection problem

Michael Fuhr
[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Fri, May 27, 2005 at 10:08:20AM +0200, FREMAUX Benjamin SOFRECOM wrote:
>
> I have effectively an index (PK) on list.group_artifact_id. I solved the
> problem with an index rebuild.

A reindex might have provided an immediate fix, but it probably
didn't solve the problem; it might happen again.  I don't know if
the problem still exists in later releases in the 7.2 branch, but
in any case I'd recommend using something newer than 7.2.2.  A
number of bugs have been fixed since then; see the Release Notes
for details.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [BUGS] BUG #1681: Selection problem

fremaux
Thank you

Michael Fuhr a écrit :

>[Please copy the mailing list on replies so others can participate
>in and learn from the discussion.]
>
>On Fri, May 27, 2005 at 10:08:20AM +0200, FREMAUX Benjamin SOFRECOM wrote:
>  
>
>>I have effectively an index (PK) on list.group_artifact_id. I solved the
>>problem with an index rebuild.
>>    
>>
>
>A reindex might have provided an immediate fix, but it probably
>didn't solve the problem; it might happen again.  I don't know if
>the problem still exists in later releases in the 7.2 branch, but
>in any case I'd recommend using something newer than 7.2.2.  A
>number of bugs have been fixed since then; see the Release Notes
>for details.
>
>  
>


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]

benjamin.fremaux.vcf (151 bytes) Download Attachment