Re: Select performance variation based on the different combinations of using where lower(), order by, and limit

On Sun, Aug 18, 2013 at 4:46 PM, Tyler Reese <jukey91(at)gmail(dot)com> wrote:

> I haven't heard of raising the statistics target, so I'll read up on that.
> A few days ago, all 4 cases were responding equally fast. I had been
> messing around with the postgres settings, and I went and dropped all of the
> indexes and recreated them just to see what would happen. I wouldn't think
> that recreating the indexes would cause case 4 to go slow, but that's the
> symptom I am seeing now. Should I be running analyze on a table after it
> has been reindexed?

PostgreSQL keeps statistics on the table's columns with the table, and
they survive a re-index. But the "column" used by the function-based
index is not a real table column. Those statistics are kept with the
index, not the table, and they do not survive the re-index. So you
should analyze the table in order to reacquire those statistics. Since
the problem is that you no longer had statistics at all for that
"column", there is probably no need to increase the statistics target,
just doing the analyze should get you back in business.

Arguably PostgreSQL's autovacuum logic should be better about dealing
with expression-based indices. But for now, a manual analyze is
needed when a new expression-based index is created, or when an
existing one is re-indexed.

Cheers,

Jeff