Re: Too many duplicated condition query return wrong value

>> Can you give an "explain (analyze, buffers)" for each query? Maybe you have a corrupted index, and one query uses the index and the other does not.

> explain (analyze, buffers) SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE "attend"."sid" = 325 AND "attend"."lid" IN ('ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', ‘ABF0010’….
>
> Index Scan using idx_attend_00 on attend (cost=0.29..627.20 rows=172 width=12) (actual time=5.158..10.179 rows=5 loops=1)
> Index Cond: (sid = 325)
> Filter: (lid = ANY ('{ABF0010,ABF0010,ABF0010,ABF0010,ABF0010 ... ABF0060,ABF0060,ABF0060,ABF0060}'::text[]))
> Rows Removed by Filter: 414
> Buffers: shared hit=331
> Total runtime: 10.196 ms
> (6 rows)

> explain (analyze, buffers) SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE "attend"."sid" = 325 AND "attend"."lid" IN ('ABF0010', 'ABF0020', 'ABF0030', 'ABF0040', 'ABF0050', 'ABF0060')
>
> Index Scan using index_attend_on_sid_and_lid on attend (cost=0.42..36.32 rows=3 width=12) (actual time=0.011..0.034 rows=6 loops=1)
> Index Cond: ((sid = 325) AND (lid = ANY ('{ABF0010,ABF0020,ABF0030,ABF0040,ABF0050,ABF0060}'::text[])))
> Buffers: shared hit=24
> Total runtime: 0.078 ms
> (4 rows)
>

Is this result aims idx_attend_00 corrupted?
How to fix it?
What countermeasure do I it?
---
http://github.com/yalab

Atsushi YOSHIDA <rudeboyjet(at)gmail(dot)com>
http://twitter.com/yalab inject your heart

> 2015/09/04 0:58、Jeff Janes <jeff(dot)janes(at)gmail(dot)com> のメール：
>
>
>
> On Thu, Sep 3, 2015 at 5:14 AM, Atsushi Yoshida <rudeboyjet(at)gmail(dot)com> wrote:
> Hi.
>
> I cought a strange result.
> I execute such query.
>
> > SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE "attend"."sid" = 325 AND "attend"."lid" IN ('ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010', 'ABF0010',
> ...
> 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060', 'ABF0060');
>
>
>
>
> it return
>
> > lid | status
> > ---------+--------
> > ABF0050 | 9
> > ABF0040 | 9
> > ABF0020 | 9
> > ABF0010 | 9
> > ABF0060 | 9
> > (5 rows)
>
> This IN condition to be unique and execute it like this.
>
> > arcvideo=> SELECT "attend"."lid", "attend"."status" FROM "attend" WHERE "attend"."sid" = 325 AND "attend"."lid" IN ('ABF0010', 'ABF0020', 'ABF0030', 'ABF0040', 'ABF0050', 'ABF0060’);
>
> It return
>
> > lid | status
> > ---------+--------
> > ABF0010 | 9
> > ABF0020 | 9
> > ABF0030 | 9
> > ABF0040 | 9
> > ABF0050 | 9
> > ABF0060 | 9
> > (6 rows)
>
> First query and second query are same meaning I think, but the result is different.
>
> Can you give an "explain (analyze, buffers)" for each query? Maybe you have a corrupted index, and one query uses the index and the other does not.
>
> Cheers,
>
> Jeff