>> 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 <[email protected]>
http://twitter.com/yalab inject your heart
> 2015/09/04 0:58、Jeff Janes <[email protected]> のメール：
>
>
>
> On Thu, Sep 3, 2015 at 5:14 AM, Atsushi Yoshida <[email protected]> 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
