I have a basic SQL call that takes forever because Postgresql seems to want to use a Seq row scan on the table Products which has around 41k rows. Below is the sql call and the explain.
explain select * from ChargeCodes where AccountID = '{58C215AA-2C71-446F-88F3-BC2C0D23EF28}' and ChargeCodeID IN (Select ChargeCodeID from Products where ProductID in (select ProductID from OrderRules where WebUserRoleID in (Select WebUserRoleID from WebUsers where WebUserID = '{3CD5D4F5-448B-11D5-83DB-0001023EA2FA}')))
Seq Scan on chargecodes (cost=0.00..19217292988.42 rows=36 width=108) Filter: ((accountid = '{58C215AA-2C71-446F-88F3-BC2C0D23EF28}'::text) AND (subplan)) SubPlan -> Materialize (cost=263250588.84..263250588.84 rows=20535 width=42) -> Seq Scan on products (cost=0.00..263250588.84 rows=20535 width=42) Filter: (subplan) SubPlan -> Materialize (cost=6409.75..6409.75 rows=554 width=42) -> Seq Scan on orderrules (cost=0.00..6409.75 rows=554 width=42) Filter: (subplan) SubPlan -> Materialize (cost=5.75..5.75 rows=1 width=42) -> Index Scan using webusers_pkey on webusers (cost=0.00..5.75 rows=1 width=42) Index Cond: (webuserid = '{3CD5D4F5-448B-11D5-83DB-0001023EA2FA}'::text)
All the above tables have proper index's, all of them excuse their individual calls quickly, but when put together it's slow as can be. In the cases of where it chose to do a Seq scan, all except the one for products are correct (tables with 10-100 rows at most).
Can anyone help and make a suggestion as to why it's doing a sequence scan, what gets even more interesting is that even if I turn force index scans on, it still seq scan's products yet when I make any individual calls to products outside of the above context it always uses the index.