Query Performance Tuning - PostgreSQL: Up and Running - page 168

only as a last resort. Although you can disable them, the planner can still use them when
it has no viable alternative. When you do see them being used, it's a good idea to double-
check that the planner is using them out of necessity, not out of ignorance. One quick
way to check is to disable them.
How Useful Is Your Index?
When the planner decides to perform a sequential scan, it plans to loop through all the
rows of a table. It opts for this route when it finds no index that could satisfy a query
condition, or it concludes that using an index is more costly than scanning the table. If
you disable the sequential scan strategy, and the planner still insists on using it, this
means that indexes are missing or that the planner thinks that the indexes you have in
place won't be helpful for the particular query. Two common mistakes people make are
to leave useful indexes out of their tables or to put in indexes that can't be used by their
queries. An easy way to check whether your indexes are used is to query the
pg_stat_user_indexes and pg_stat_user_tables views using the pg_stat_state
ments extension described in “Gathering Statistics on Statements” on page 166 .
Let's start off with a query against the table we created in Example 7-18 . We'll add a GIN
index on the array column. GIN indexes are among the few indexes you can use to index
arrays:
CREATE INDEX idx_lu_fact_types ON census . lu_fact_types USING gin ( fact_subcats );
To test our index, we'll execute a query to find all rows with subcats containing “White
alone” or “Asian alone.” We explicitly enabled sequential scan even though it's the default
setting, just to be sure. The accompanying EXPLAIN output is shown in Example 9-8 .
Example 9-8. Allow planner choose sequential scan
set enable_seqscan = true ;
EXPLAIN ( ANALYZE )
SELECT *
FROM census . lu_fact_types
WHERE fact_subcats && '{White alone, Black alone}' :: varchar [];
Seq Scan on lu_fact_types
(cost=0.00..2.85 rows=2 width=200) (actual time=0.066..0.076 rows=2 loops=1)
Filter: (fact_subcats && '{"White alone","Black alone"}'::character varying[]) Rows
Removed by Filter: 66
Planning time: 0.182 ms
Execution time: 0.108 ms
Observe that when enable_seqscan is enabled, our index is not being used and the
planner has chosen to do a sequential scan. This could be because our table is so small
or because the index we have is no good for this query. If we repeat the query but turn
off sequential scan beforehand, as shown in Example 9-9 , we can see that we have suc‐
ceeded in forcing the planner to use the index.