From: Richard Huxton <dev@archonet.com>
To: Mezei Zoltán <mezei.zoltan@telefor.hu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deceiding which index to use
Date: Fri, 09 Mar 2007 15:03:54 +0000
Message-ID: <45F1775A.8030701@archonet.com> (raw)
In-Reply-To: <45F174CF.8010302@telefor.hu>
References: <45F15F0F.8080403@telefor.hu>
<45F1715E.1050000@archonet.com>
<45F174CF.8010302@telefor.hu>
Mezei Zoltán wrote:
> Richard Huxton wrote:
> > Mezei Zoltán wrote:
> > Q1. Why are you storing a numeric in a varchar?
>
> Because it's not always numeric info. :/
>
> > Q2. How many unique values does anumber have? And how many rows in
> > subscriber?
>
> About 10k distinct anumbers and 20k rows. Nothing special...
And does the planner know that?
SELECT * FROM pg_stats WHERE tablename='subscriber' AND attname='anumber';
It's the n_distinct you're interested in, and perhaps most_common_freqs.
> > Q3. What happens if you create the index on plain (anumber) and then
> > test against '555555555'?
>
> Nothing, everything is the same - the problem lies on the other table's index
> usage, using this index is fine.
The planner has to guess how many matches it will have for
subscriber=5555555. Based on that choice, it will either:
a. Do the join, then find the highest crd values (sort)
b. Scan the crd values backwards and then join
It's chosen (b) because it's estimating the numbers of matches
incorrectly. I'm wondering whether the system can't see through your
function-call (the cast to numeric) to determine how many matches it's
going to get for any given value.
If the system can't be persuaded into getting its estimates more
accurate, it might be worth trying an index on (subscriber_id,crd) and
dropping the index on (crd) - if that's reasonable for your query patterns.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, mezei.zoltan@telefor.hu
Subject: Re: Deceiding which index to use
In-Reply-To: <45F1775A.8030701@archonet.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