Re: Deceiding which index to use

Re: Deceiding which index to use

Mezei Zoltán wrote:
> 
> 
> 
>   
> 
> 
> Richard Huxton wrote:
> 

>   
>   
>   Re: [PERFORM] Deceiding which index to use
> 

Would you mind instructing your mail system to skip converting your
text/plain messages to HTML?  It's kind of annoying for some of us.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Deceiding which index to use

Re: Deceiding which index to use

Mezei Zoltán wrote:
> Hi!
> 
> I have two tables with some indices on them:
> 
> CREATE TABLE subscriber
> (
>   id serial NOT NULL,
>   anumber character varying(32) NOT NULL,
>   CONSTRAINT subscriber_pk PRIMARY KEY (id)
> )
> 
> CREATE INDEX anumber_idx_numeric
>   ON subscriber
>   USING btree
>   (anumber::numeric);

> I would like to run a query like this one:
> 
> select l.id
> from output_message_log l join subscriber s on l.subscriber_id = s.id
> where s.anumber::numeric = 5555555555
> order by l.crd desc
> limit 41
> offset 20

Q1. Why are you storing a numeric in a varchar?
Q2. How many unique values does anumber have? And how many rows in 
subscriber?
Q3. What happens if you create the index on plain (anumber) and then 
test against '555555555'?

-- 
   Richard Huxton
   Archonet Ltd

Re: Deceiding which index to use

Re: Deceiding which index to use

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

Re: Deceiding which index to use

Re: Deceiding which index to use

Mezei Zoltán wrote:
> Richard Huxton wrote:
>>
>> 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.
>>
> n_distinct is -0.359322 and most_common_vals contains about 10 different 
> anumbers (which are corretct), most_common_freqs are between 0.01 and 0.001. 
> What does n_distinct exactly mean? Why is it negative?

It's saying that it's a ratio, so if you doubled the number of 
subscribers it would expect that the number of unique anumber's would 
double too. So you've got about 36% of the rows with unique values - 
pretty much what you said earlier. That's not bad, since the planner 
only uses an estimate.

OK - so the next place to look is the distribution of values for 
subscriber_id on the output_message_log. Does that have some subscribers 
with many rows and lots with hardly any? If so, you might need to 
increase the stats on that column:

ALTER TABLE output_message_log ALTER COLUMN subscriber_id SET STATISTICS 
;
ANALYSE output_message_log (subscriber_id);

The  defaults to 10, but can be set as high as 1000. You want to 
try and capture the "big" subscribers.

-- 
   Richard Huxton
   Archonet Ltd

Re: Deceiding which index to use

Re: Deceiding which index to use

Mezei Zoltán wrote:
> Richard Huxton wrote:
>>
>> OK - so the next place to look is the distribution of values for
>> subscriber_id on the output_message_log. Does that have some subscribers
>> with many rows and lots with hardly any?
>>
> Hmm... There are about 1.5k subscribers with 100-200 messages each - all the 
> other 19k has an average of 8.9 messages, most of them having only 1 message. I 
> think that's exactly the situation you mention...

[snip alter table ... set statistics]

> So if I'm correct: this statistics gathering can be fine tuned, and if i set the 
>  to 1000 then not only the first 10 subsribers (with most messages) will be 
> stored in pg_stats, but the first 1000? Is 1000 a hard-coded 
> highest-possible-value? I think it would be best to set that to simething like 
> 1800-1900 as I have about that many subscibers with high message count.

There is a cost to increasing the stats values, otherwise it'd already 
be set at 1000. In your case I'm not sure if 100-200 vs 8-9 messages is 
enough to skew things. Only one way to find out...

-- 
   Richard Huxton
   Archonet Ltd

Re: Deceiding which index to use

Re: Deceiding which index to use

Alvaro Herrera wrote:
>
> Would you mind instructing your mail system to skip converting your
> text/plain messages to HTML?  It's kind of annoying for some of us.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
Sorry about that - is this message OK now?

Zizi

Re: Deceiding which index to use

Mezei Zoltán wrote:
> Alvaro Herrera wrote:
>>
>> Would you mind instructing your mail system to skip converting your
>> text/plain messages to HTML?  It's kind of annoying for some of us.
>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>>
> Sorry about that - is this message OK now?

That's done the trick.

-- 
   Richard Huxton
   Archonet Ltd

Re: Deceiding which index to use

Mezei Zoltán wrote:
> Richard Huxton wrote:
>> 
>> There is a cost to increasing the stats values, otherwise it'd
>> already be set at 1000. In your case I'm not sure if 100-200 vs 8-9
>> messages is enough to skew things. Only one way to find out...
>> 
> Well, I tried. The situation is:
> 
> - when I look for a subscriber which the planner thinks to have more
> messages --> it uses the index on crd - when I look for a subscriber
> which the planner thinks to have less messages --> it uses the index
> on subscriber_id
> 
> I think that's OK, even if it still don't do what I really would like
> to see it do :-)

Well, it's decision about where the costs cross over will be in the 
other "cost" settings in postgresql.conf. The thing is, it's not worth 
tuning for just this one query, because you can easily end up running 
everything else more slowly.

> Thanks for all your help, maybe I know a little bit more about
> poostgresql than before.


-- 
   Richard Huxton
   Archonet Ltd