Re: Slow first query despite LIMIT and OFFSET clause

Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> writes:

> Index Cond: ((user_id)::text = 'superman'::text)
> Filter: (status = 'Y'::bpchar)

> Of course for unregistered users we use user_known = 0, so they are
> excluded from this index. Is this not a useful partial index? I think
> in this SQL, the user_id is always "superman" and the user_known
> always 1 which is why the guesstimate from the planner may be off?

Well the histograms are for each column separately, so the planner will take
the selectivity estimates for user_id='superman' and status = 'Y' and multiply
them.

If the "status" of 'superman' records are very different from the status
records as a whole then this will give poor results.

If that's not the case then raising the statistics target for those two
columns might help. And of course if the table hasn't been analyzed recently
then analyzing it more often is always good.

There isn't really a good solution for cross-column stats. You could perhaps
create a functional index (could still be partial too) on an expression like

CASE WHEN status = 'Y' THEN superman

Which will make Postgres build stats for the result of that expression
specifically. Then if you use that expression exactly as-is in the query the
planner should those statistics. I think. I haven't tried this... Tell us how
it goes :)

I wonder if we should look at building "partial" histograms for the columns in
partial indexes effectively equivalent to this... hm...

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!