Lack of index usage when doing array casts

From: Alexey Klyukin <alexk(at)hintbits(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Lack of index usage when doing array casts
Date: 2014-02-21 18:01:09
Message-ID: CAAS3ty+GD9xfh4M4OiGLSQxHRyQGUS1VUaofdxqCn0A=_z4rRw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

We had a problem with PostgreSQL not using an index scan in 2 similar
queries, the only difference between them is the array cast from text[] to
location_type[] (array of enum values).

The execution plans are the following:

1.
Hash Join (cost=1.68..64194.88 rows=962149 width=62) (actual
time=0.096..3580.542 rows=62 loops=1)
Hash Cond: (location.topology_id = topology.t_id)
-> Seq Scan on location (cost=0.00..34126.05 rows=962149 width=58)
(actual time=0.031..3580.261 rows=62 loops=1)
Filter: (type = ANY
(('{CITY,VILLAGE,TOWN,ROOM}'::text[])::location_type[]))
-> Hash (cost=1.30..1.30 rows=30 width=8) (actual time=0.041..0.041
rows=31 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on topology (cost=0.00..1.30 rows=30 width=8) (actual
time=0.005..0.019 rows=31 loops=1)
Total runtime: 3580.604 ms

2.
Hash Join (cost=29.91..3649.53 rows=1435 width=62) (actual
time=0.366..0.811 rows=62 loops=1)
Hash Cond: (location.topology_id = topology.t_id)
-> Bitmap Heap Scan on location (cost=28.24..3603.01 rows=1435
width=58) (actual time=0.239..0.311 rows=62 loops=1)
Recheck Cond: (type = ANY
('{CITY,VILLAGE,TOWN,ROOM}'::location_type[]))
-> Bitmap Index Scan on location_type_idx (cost=0.00..27.88
rows=1435 width=0) (actual time=0.223..0.223 rows=62 loops=1)
Index Cond: (type = ANY
('{CITY,VILLAGE,TOWN,ROOM}'::location_type[]))
-> Hash (cost=1.30..1.30 rows=30 width=8) (actual time=0.076..0.076
rows=31 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on topology (cost=0.00..1.30 rows=30 width=8) (actual
time=0.019..0.041 rows=31 loops=1)
Total runtime: 0.934 ms

The problematic line is this one:

-> Seq Scan on location (cost=0.00..34126.05 rows=962149 width=58)
(actual time=0.031..3580.261 rows=62 loops=1)
Filter: (type = ANY
(('{CITY,VILLAGE,TOWN,ROOM}'::text[])::location_type[]))

The PostgreSQL version this query is running is 9.3.2.

Is it expected that index is not used during such a cast? If so, what would
be the better way to force the index usage when doing array casts?

Sincerely,
--
Alexey Klyukin

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2014-02-21 19:04:57 Re: Lack of index usage when doing array casts
Previous Message Ashutosh Durugkar 2014-02-21 12:22:08 Postgresql tunning-- help needed