Forum .LRN Q&A: Re: .LRN v2 Performance

Collapse
12: Re: .LRN v2 Performance (response to 1)
Posted by Don Baccus on
A big heads-up for PostgreSQL and .LRN which I'm trying to wrap my head around is the fact that on Galileo's installation, certain queries buried inside PL/pgSQL do sequential scans rather than use the primary key index.

With about 1.6 million objects this is annoying, to put it kindly.  When executed in PSQL the index is used.

(this is the situation Rocael refered to rather cryptically above).

I've eliminated various possibilities of the "we just have to force the function to be re-compiled after all the data's been loaded and ANALYZE'd" variety and now believe that it has to do with the optimizer's default notion of how many rows will be processed by a query when an unknown variable rather than specific key appears in the query.

Anyway we're seeing these simple PL/pgSQL calls to the CR take several seconds rather than the milliseconds it takes to do the query inline.

More to come ...