Phoenix Kiula <phoenix.kiula@xxxxxxxxx> 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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
