From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Shantanu Shekhar <shekharshan(at)yahoo(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Stats for indexes on expressions |
Date: | 2020-12-25 06:09:02 |
Message-ID: | 1135857.1608876542@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Shantanu Shekhar <shekharshan(at)yahoo(dot)com> writes:
> I am trying to understand how the cost for a query involving indexes on expressions is calculated. How is the statistics on the expression maintained? For example Postgres documentation on 'Indexes on Expressions' mentions the following example:
> CREATE INDEX people_names ON people ((first_name || ' ' || last_name));SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
> The index is created on an expression involving first name and last name. My confusion is basically around:
> (1) When using this index how will the query planner get the stats on this expression?
ANALYZE on a table will (re)build statistics for any expression indexes on
that table, in addition to the stats for the table's own columns.
(2) Whenever an index on an expression is defined like this, should we rebuild the stats?
If you don't want to wait around for auto-analyze to do it, yes.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2020-12-25 13:52:28 | How to specify that a trigger should fire when column is NOT in SET-clause? |
Previous Message | Shantanu Shekhar | 2020-12-25 05:32:16 | Stats for indexes on expressions |