From: Jeff Davis <pgsql@j-davis.com>
To: Rainer Mager <rainer@vanten.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: why does this use the wrong index?
Date: Fri, 19 Sep 2008 11:25:28 -0700
Message-ID: <1221848728.6194.292.camel@dell.linuxdev.us.dell.com> (raw)
In-Reply-To: <000101c919ef$7c0b8e20$7422aa60$@com>
References: <000101c919ef$7c0b8e20$7422aa60$@com>
> So, What can I do to encourage Postgres to use the first index even when the
> date range is smaller.
>
It looks like PostgreSQL is estimating the selectivity of your date
ranges poorly. In the second (bad) plan it estimates that the index scan
with the filter will return 1 row (and that's probably because it
estimates that the date range you specify will match only one row).
This leads PostgreSQL to choose the narrower index because, if the index
scan is only going to return one row anyway, it might as well scan the
smaller index.
What's the n_distinct for start_time?
=> select n_distinct from pg_stats where tablename='ad_log' and
attname='start_time';
If n_distinct is near -1, that would explain why it thinks that it will
only get one result.
Based on the difference between the good index scan (takes 0.056ms per
loop) and the bad index scan with the filter (311ms per loop), the
"player" condition must be very selective, but PostgreSQL doesn't care
because it already thinks that the date range is selective.
Regards,
Jeff Davis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql@j-davis.com, rainer@vanten.com
Subject: Re: why does this use the wrong index?
In-Reply-To: <1221848728.6194.292.camel@dell.linuxdev.us.dell.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox