Re: Planner chooses slow index heap scan despite accurate row estimates

Tom Lane-2 wrote
> Jake Magner <

> jakemagner90@

> > writes:
>> I tried without doing an INSERT at all, just running the SELECT queries
>> and
>> the result is the same. Nested loop is chosen but is much slower.
>
> FWIW, I just noticed that the comparisons you're using are plain equality
> of the arrays. While a GIN array index supports that, it's not exactly
> its strong suit: the sort of questions that index type supports well are
> more like "which arrays contain value X?". I wonder if it'd be worth
> creating btree indexes on the array column.

I added btree indexes and now the nested loop uses those and is a bit faster
than the hash join. So the planner just misestimates the cost of doing the
equality comparisons? I'd prefer not to add more indexes, the hash join
performance is fast enough if it would just choose that but I'm reluctant to
turn off nested loops in case the table gets a lot bigger.

--
View this message in context: http://postgresql.nabble.com/Planner-chooses-slow-index-heap-scan-despite-accurate-row-estimates-tp5905357p5905453.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.