Ayub Khan <ayub.hp@xxxxxxxxx> writes: > I checked all the indexes are defined on the tables however the query seems > slow, below is the plan. Can any one give any pointers to verify ? You might try to do something about the poor selectivity estimate here: > -> Index Scan using > menu_item_restaurant_id on menu_item a (cost=0.42..177.31 rows=1 > width=87) (actual time=0.130..3.769 rows=89 loops=1) > Index Cond: (restaurant_id = 1528) > " Filter: ((active = > 'Y'::bpchar) AND (is_menu_item_available(menu_item_id, 'Y'::bpchar) = > 'Y'::bpchar))" > Rows Removed by Filter: 194 If the planner realized that this'd produce O(100) rows not 1, it'd likely have picked a different plan. I'm guessing that the issue is lack of knowledge about what is_menu_item_available() will do. Maybe you could replace that with a status column? regards, tom lane
