Thread: : PostgreSQL Index behavior
DISCLAIMER: Please note that this message and any attachments may contain confidential and proprietary material and information and are intended only for the use of the intended recipient(s). If you are not the intended recipient, you are hereby notified that any review, use, disclosure, dissemination, distribution or copying of this message and any attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender and delete this e-mail , whether electronic or printed. Please also note that any views, opinions, conclusions or commitments expressed in this message are those of the individual sender and do not necessarily reflect the views of Ver sé Innovation Pvt Ltd.
On Mon, Sep 10, 2012 at 5:39 AM, Venkat Balaji <venkat.balaji@verse.in> wrote: > Hello Community, > > I intend to understand further on PostgreSQL Index behavior on a "SELECT" > statement. > > We have a situation where-in Index on unique column is not being picked up > as expected when used with-in the WHERE clause with other non-unique columns > using AND operator. > > explain SELECT tv.short_code, tv.chn as pkg_subscription_chn, > tv.vert as pkg_vert, ubs.campaign_id as campaign, > 'none'::varchar as referer, > CAST('CAMPAIGNWISE_SUBSCRIBER_BASE' AS VARCHAR) as vn, > count(tv.msisdn) as n_count, '0'::numeric AS tot_revenue > FROM campaign_base ubs > JOIN tab_current_day_v2 tv > ON ubs.ubs_seq_id = tv.ubs_seq_id > AND tv.dt = CAST('2012-09-08' AS DATE) > GROUP BY tv.short_code, tv.vert, tv.chn, ubs.campaign_id, vn; ... > > The above plan shows "seq scan" on tab_current_day_v2 table, though there is > an index on "ubs_seq_id" column which is an unique column. > > Can anyone please help us understand, why PostgreSQL optimizer is not > prioritizing the unique column and hitting ubs_seq_id_idx Index here ? The query where clause does not specify a constant value for ubs_seq_id. So it is likely that the only way to use that index would be to reverse the order of the nested loop and seq scan the other table. Is there any reason to think that doing that would be faster? > > Later - > > We have created composite Index on "dt" (one distinct value) and > "ubs_seq_id" (no duplicate values) and the index has been picked up. Postgres seems to think that "dt" has no duplicate values, the opposite of having one distinct value. That is based on the estimates given in the explain plan, that teh seq scan will return only one row after the filter on Filter: "(dt = '2012-09-08'::date)". This does seem to conflict with what you report from pg_stats, but I'm not familiar with that view, and you haven't told us what version of pgsql you are using. > Below is the scenario where-in the same query's plan picking up the > composite Index. It is only using the first column of that composite index. So if you built a single column index just on dt, it would be picked up as well. Cheers, Jeff
> explain SELECT tv.short_code, tv.chn as pkg_subscription_chn,...
> tv.vert as pkg_vert, ubs.campaign_id as campaign,
> 'none'::varchar as referer,
> CAST('CAMPAIGNWISE_SUBSCRIBER_BASE' AS VARCHAR) as vn,
> count(tv.msisdn) as n_count, '0'::numeric AS tot_revenue
> FROM campaign_base ubs
> JOIN tab_current_day_v2 tv
> ON ubs.ubs_seq_id = tv.ubs_seq_id
> AND tv.dt = CAST('2012-09-08' AS DATE)
> GROUP BY tv.short_code, tv.vert, tv.chn, ubs.campaign_id, vn;>The query where clause does not specify a constant value for
> The above plan shows "seq scan" on tab_current_day_v2 table, though there is
> an index on "ubs_seq_id" column which is an unique column.
>
> Can anyone please help us understand, why PostgreSQL optimizer is not
> prioritizing the unique column and hitting ubs_seq_id_idx Index here ?
ubs_seq_id. So it is likely that the only way to use that index would
be to reverse the order of the nested loop and seq scan the other
table. Is there any reason to think that doing that would be faster?
> Later -Postgres seems to think that "dt" has no duplicate values, the
>
> We have created composite Index on "dt" (one distinct value) and
> "ubs_seq_id" (no duplicate values) and the index has been picked up.
opposite of having one distinct value.
That is based on the estimates given in the explain plan, that teh seq
scan will return only one row after the filter on Filter: "(dt =
'2012-09-08'::date)". This does seem to conflict with what you
report from pg_stats, but I'm not familiar with that view, and you
haven't told us what version of pgsql you are using.
DISCLAIMER: Please note that this message and any attachments may contain confidential and proprietary material and information and are intended only for the use of the intended recipient(s). If you are not the intended recipient, you are hereby notified that any review, use, disclosure, dissemination, distribution or copying of this message and any attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender and delete this e-mail , whether electronic or printed. Please also note that any views, opinions, conclusions or commitments expressed in this message are those of the individual sender and do not necessarily reflect the views of Ver sé Innovation Pvt Ltd.
On Wed, Sep 12, 2012 at 12:57 AM, Venkat Balaji <venkat.balaji@verse.in> wrote: > We are using PostgreSQL-9.0.1. You are missing almost 2 years of updates, bug fixes, and security fixes.
On Wed, Sep 12, 2012 at 12:57 AM, Venkat Balaji <venkat.balaji@verse.in> wrote:
> We are using PostgreSQL-9.0.1.
You are missing almost 2 years of updates, bug fixes, and security fixes.
DISCLAIMER: Please note that this message and any attachments may contain confidential and proprietary material and information and are intended only for the use of the intended recipient(s). If you are not the intended recipient, you are hereby notified that any review, use, disclosure, dissemination, distribution or copying of this message and any attachments is strictly prohibited. If you have received this email in error, please immediately notify the sender and delete this e-mail , whether electronic or printed. Please also note that any views, opinions, conclusions or commitments expressed in this message are those of the individual sender and do not necessarily reflect the views of Ver sé Innovation Pvt Ltd.
Dne 13.09.2012 11:22, Venkat Balaji napsal: > On Wed, Sep 12, 2012 at 7:42 PM, Scott Marlowe > <scott.marlowe@gmail.com [2]> wrote: > >> On Wed, Sep 12, 2012 at 12:57 AM, Venkat Balaji >> <venkat.balaji@verse.in [1]> wrote: >> >> > We are using PostgreSQL-9.0.1. >> >> You are missing almost 2 years of updates, bug fixes, and security >> fixes. > > Thank you Scott, We are planning to upgrade to the latest version > (9.2) in the near future. That was not the point. The last minor update in this branch (9.0) is 9.0.9. You're missing fixes and improvements that happened between 9.0.1 and 9.0.9, that's what Scott probably meant. And some of those fixes may be quite important, so do the upgrade ASAP. These minor updates are binary compatible, so all you need to do is shutting down the DB, updating the binaries (e.g. installing a new package) and starting the database again. Upgrading to 9.2 means you'll have to do a dump/restore and possibly more. Tomas