Re: GIN INdex is not used with && operator for a text array index

balasubramanian c r <crbs(dot)siebel(at)gmail(dot)com> writes:
> when Operator '@>' is used the index is used and the execution time is 60ms.

Yeah ... note that it's predicted to return just one row, and that
guess is correct:

> Bitmap Heap Scan on public.address18 (cost=261.25..262.52 rows=1 width=4)
> (actual time=58.992..58.994 rows=1 loops=1)

> when Operator '&&' is used the index is used and the execution time is 60ms.
> It is performing sequential scan which is not expected.

I do not see why you expect that. This case retrieves many more rows
than the other one, both in the planner's estimate and in reality:

> Seq Scan on public.address18 (cost=0.00..77215.11 rows=247741 width=4)
> (actual time=0.063..1880.467 rows=247741 loops=1)

An indexscan is not necessarily better than a seqscan for such cases...

> When i disable the sequential scan the execution time is increased
> significantly.

... so, indeed, the planner was correct to use a seqscan.

> Not expecting this behavior currently.

Your expectation is faulty.

regards, tom lane