Why isn't index used for this query?

Question

I have a table with 300K rows and b-tree index on "operator" field. While I'm runnig this query it isn't using an index. "operator" has the same data-type as dict.vw_dict_operator.id.

EXPLAIN SELECT  
    id,
    name
FROM
    dict.vw_dict_operator self 
WHERE

            EXISTS (
                SELECT 42 FROM ti.ti_flight_availability flight_avail
                WHERE flight_avail.operator = self.id
            )   
ORDER BY
    self.name 

"Sort  (cost=3349.66..3351.02 rows=545 width=18)"
"  Sort Key: dict_operator.name"
"  ->  Seq Scan on dict_operator  (cost=0.00..3324.89 rows=545 width=18)"
"        Filter: ((NOT trash) AND (subplan))"
"        SubPlan"
"          ->  Seq Scan on ti_flight_availability flight_avail  (cost=0.00..8513.66 rows=3750 width=0)"
"                Filter: (operator = $0)"

UPD: thanks @gbn. index isnot used when joining tables as well

EXPLAIN SELECT self.id, self.name 
FROM dict.vw_dict_operator self JOIN ti.ti_flight_availability flight_avail
ON flight_avail.operator = self.id

"Nested Loop  (cost=0.00..92988.47 rows=228639 width=18)"
"  ->  Seq Scan on ti_flight_availability flight_avail  (cost=0.00..7754.33 rows=303733 width=4)"
"  ->  Index Scan using pk_dict_operator on dict_operator  (cost=0.00..0.27 rows=1 width=18)"
"        Index Cond: (dict_operator.id = flight_avail.operator)"
"        Filter: (NOT dict_operator.trash)"

Answer 1

Would it use the index if you did a join instead?

Answer 2

What index you have have on dict.vw_dict_operator?

EXISTS is a form of JOIN (simplistic, I know) and it's possible the index is ignored because there is no convenient one to JOIN to. So it scans instead.

Edit:

the JOIN plan is not using an index on ti_flight_availability either... but you state you have an index on it?

Answer 3

Why don't you use a JOIN? And did you ANALYZE? What about the statistics? Check pg_stats for this table to get some more information. reltuples and relpages in pg_class are also interesting for the table and it's indexes.

---

Edit: The JOIN expects 228639 rows. The sequential scan expects 303733 rows, only a fraction extra. When these 100k records are all over the place, the database has to scan the relpages anyway. A sequential scan will be faster than an index scan, a sequential scan a (fast) sequentail read, an index scan will be two (!) slow random reads: the information from the index and the data from the table.

If you think the plan is wrong, ANALYZE the table and show us information from pg_stats and pg_class about the tables and indexes.

ANALYZE;

SELECT relpage, reltuples WHERE relname = 'table_or_index_name';

SELECT * FROM pg_stats WHERE tablename = 'name of your table';