From mezei.zoltan@telefor.hu Fri Mar 9 13:53:38 2007 Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 7B6679FB577 for ; Fri, 9 Mar 2007 09:53:38 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 59136-08 for ; Fri, 9 Mar 2007 09:53:25 -0400 (AST) X-Greylist: delayed 00:33:08.50325 by SQLgrey-1.7.4 Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id 651B19FB595 for ; Fri, 9 Mar 2007 09:53:30 -0400 (AST) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id D1CAE1C055 for ; Fri, 9 Mar 2007 14:20:15 +0100 (CET) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Fri, 9 Mar 2007 14:20:15 +0100 Message-ID: <45F15F0F.8080403@telefor.hu> Date: Fri, 09 Mar 2007 14:20:15 +0100 From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Organization: Telefor kft. User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Deceiding which index to use Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 09 Mar 2007 13:20:15.0659 (UTC) FILETIME=[ACED5FB0:01C7624D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=BAYES_50, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200703/191 X-Sequence-Number: 23596 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);

CREATE TABLE output_message_log
(
  id serial NOT NULL,
  subscriber_id integer NOT NULL,
  crd timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT output_message_log_pk PRIMARY KEY (id),
  CONSTRAINT subscriber_fk FOREIGN KEY (subscriber_id)
      REFERENCES subscriber (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
)

CREATE INDEX crd_idx
  ON output_message_log
  USING btree
  (crd);

CREATE INDEX subscriber_id_idx
  ON output_message_log
  USING btree
  (subscriber_id);

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

The thing I do not understand is why postgresql wants to use crd_idx:

"Limit  (cost=4848.58..14788.18 rows=41 width=12) (actual time=7277.115..8583.814 rows=41 loops=1)"
"  ->  Nested Loop  (cost=0.00..1195418.42 rows=4931 width=12) (actual time=92.083..8583.713 rows=61 loops=1)"
"        ->  Index Scan Backward using crd_idx on output_message_log l  (cost=0.00..17463.80 rows=388646 width=16) (actual time=0.029..975.095 rows=271447 loops=1)"
"        ->  Index Scan using subscriber_pk on subscriber s  (cost=0.00..3.02 rows=1 width=4) (actual time=0.026..0.026 rows=0 loops=271447)"
"              Index Cond: ("outer".subscriber_id = s.id)"
"              Filter: ((anumber)::numeric = 36308504669::numeric)"
"Total runtime: 8584.016 ms"

I would like postgresql to use subscriber_id_idx which resulst in a far less execution time on this database.

I tried to lower random_page_cost, but that didn't help as an index is already used, just not the "good" one.

Could you please comment on this issue and suggest some possible soulutions?

Thanks,

Zizi

 From dev@archonet.com Fri Mar 9 14:38:34 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 35CE79FB607 for ; Fri, 9 Mar 2007 10:38:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 90120-04 for ; Fri, 9 Mar 2007 10:38:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 458249FB735 for ; Fri, 9 Mar 2007 10:38:29 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 3E8C276686; Fri, 9 Mar 2007 14:38:23 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 1FCA427401E; Fri, 9 Mar 2007 14:38:23 +0000 (GMT) Message-ID: <45F1715E.1050000@archonet.com> Date: Fri, 09 Mar 2007 14:38:22 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Cc: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> In-Reply-To: <45F15F0F.8080403@telefor.hu> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.203 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/193 X-Sequence-Number: 23598 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 From mezei.zoltan@telefor.hu Fri Mar 9 14:53:16 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 14C289FB765 for ; Fri, 9 Mar 2007 10:53:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 34138-06 for ; Fri, 9 Mar 2007 10:53:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id E22A79FB693 for ; Fri, 9 Mar 2007 10:53:09 -0400 (AST) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id EF4F81C093; Fri, 9 Mar 2007 15:53:03 +0100 (CET) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Fri, 9 Mar 2007 15:53:03 +0100 Message-ID: <45F174CF.8010302@telefor.hu> Date: Fri, 09 Mar 2007 15:53:03 +0100 From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Organization: Telefor kft. User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: Richard Huxton CC: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> In-Reply-To: <45F1715E.1050000@archonet.com> Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 09 Mar 2007 14:53:03.0743 (UTC) FILETIME=[A3C390F0:01C7625A] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.301 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_40_50, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200703/194 X-Sequence-Number: 23599 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...

> 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.

Zizi
 From dev@archonet.com Fri Mar 9 15:04:43 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 171F59FB577 for ; Fri, 9 Mar 2007 11:04:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 39242-01 for ; Fri, 9 Mar 2007 11:04:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id D46629FB4F1 for ; Fri, 9 Mar 2007 11:04:38 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 20EE2750E6; Fri, 9 Mar 2007 15:03:55 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 089BE27403D; Fri, 9 Mar 2007 15:03:55 +0000 (GMT) Message-ID: <45F1775A.8030701@archonet.com> Date: Fri, 09 Mar 2007 15:03:54 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Cc: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> In-Reply-To: <45F174CF.8010302@telefor.hu> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.202 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/195 X-Sequence-Number: 23600 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 From mezei.zoltan@telefor.hu Fri Mar 9 15:13:55 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 0B33E9FBB5A for ; Fri, 9 Mar 2007 11:13:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 67626-03 for ; Fri, 9 Mar 2007 11:13:43 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id 5F1509FBB36 for ; Fri, 9 Mar 2007 11:13:49 -0400 (AST) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id 571311C09B; Fri, 9 Mar 2007 16:13:47 +0100 (CET) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Fri, 9 Mar 2007 16:13:47 +0100 Message-ID: <45F179AB.7040000@telefor.hu> Date: Fri, 09 Mar 2007 16:13:47 +0100 From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Organization: Telefor kft. User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: Richard Huxton CC: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> In-Reply-To: <45F1775A.8030701@archonet.com> Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-OriginalArrivalTime: 09 Mar 2007 15:13:47.0158 (UTC) FILETIME=[88E5A360:01C7625D] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.364 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_30_40, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200703/196 X-Sequence-Number: 23601 Richard Huxton wrote:
Re: [PERFORM] Deceiding which index to use

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?

> 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.

It can see through the cast - I have just tried to create the same database omitting the non-numeric anumbers and the results are the same.

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.

I'll try that one if the negative n_distinct value can be a correct one :-)

Zizi
From dev@archonet.com Fri Mar 9 15:21:44 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C4C859FA281 for ; Fri, 9 Mar 2007 11:21:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 96696-02-6 for ; Fri, 9 Mar 2007 11:21:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 28C0A9FB593 for ; Fri, 9 Mar 2007 11:21:22 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id BEE6173C4E; Fri, 9 Mar 2007 15:21:20 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id A5FBA27403D; Fri, 9 Mar 2007 15:21:20 +0000 (GMT) Message-ID: <45F17B70.60001@archonet.com> Date: Fri, 09 Mar 2007 15:21:20 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Cc: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> In-Reply-To: <45F179AB.7040000@telefor.hu> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.202 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/197 X-Sequence-Number: 23602 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 From mezei.zoltan@telefor.hu Fri Mar 9 15:44:33 2007 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 630449FBAF4 for ; Fri, 9 Mar 2007 11:44:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 70720-03 for ; Fri, 9 Mar 2007 11:44:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id 005AF9FB591 for ; Fri, 9 Mar 2007 11:44:20 -0400 (AST) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id BA9611C09F; Fri, 9 Mar 2007 16:44:18 +0100 (CET) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Fri, 9 Mar 2007 16:44:18 +0100 Message-ID: <45F180D2.5080201@telefor.hu> Date: Fri, 09 Mar 2007 16:44:18 +0100 From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Organization: Telefor kft. User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: Richard Huxton CC: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com> In-Reply-To: <45F17B70.60001@archonet.com> Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 09 Mar 2007 15:44:18.0533 (UTC) FILETIME=[CC7B4950:01C76261] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.385 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_30_40, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200703/198 X-Sequence-Number: 23603 Richard Huxton wrote:
Re: [PERFORM] Deceiding which index to use

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...

If so, you might need to
increase the stats on that column:

ALTER TABLE output_message_log ALTER COLUMN subscriber_id SET STATISTICS
<num>;
ANALYSE output_message_log (subscriber_id);

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

So if I'm correct: this statistics gathering can be fine tuned, and if i set the <num> 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.

Zizi
 From dev@archonet.com Fri Mar 9 15:52:43 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4D6589FBC57 for ; Fri, 9 Mar 2007 11:52:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 53053-01 for ; Fri, 9 Mar 2007 11:52:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 89E989FBC67 for ; Fri, 9 Mar 2007 11:52:25 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id F07C773BCF; Fri, 9 Mar 2007 15:52:23 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id CB5A827403D; Fri, 9 Mar 2007 15:52:23 +0000 (GMT) Message-ID: <45F182B7.30507@archonet.com> Date: Fri, 09 Mar 2007 15:52:23 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Cc: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com> <45F180D2.5080201@telefor.hu> In-Reply-To: <45F180D2.5080201@telefor.hu> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.202 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/199 X-Sequence-Number: 23604 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 From alvherre@commandprompt.com Fri Mar 9 16:00:25 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 40BCB9FBBB9 for ; Fri, 9 Mar 2007 12:00:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 20851-01 for ; Fri, 9 Mar 2007 12:00:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 106179FB69E for ; Fri, 9 Mar 2007 12:00:16 -0400 (AST) Received: from perhan.alvh.no-ip.org (201-221-201-67.bk11-dsl.surnet.cl [201.221.201.67]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l29G0Ie5023576; Fri, 9 Mar 2007 08:00:19 -0800 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 171AF329F5; Fri, 9 Mar 2007 13:00:09 -0300 (CLST) Date: Fri, 9 Mar 2007 13:00:09 -0300 From: Alvaro Herrera To: Mezei =?iso-8859-1?Q?Zolt=E1n?= Cc: Richard Huxton , pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use Message-ID: <20070309160009.GC10875@alvh.no-ip.org> Mail-Followup-To: Mezei =?iso-8859-1?Q?Zolt=E1n?= , Richard Huxton , pgsql-performance@postgresql.org References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com> <45F180D2.5080201@telefor.hu> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <45F180D2.5080201@telefor.hu> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Fri, 09 Mar 2007 08:00:21 -0800 (PST) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.194 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/200 X-Sequence-Number: 23605 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 From mezei.zoltan@telefor.hu Fri Mar 9 16:07:32 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 06FD19FBD19 for ; Fri, 9 Mar 2007 12:07:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 04035-09 for ; Fri, 9 Mar 2007 12:07:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id 31ADE9FBCF9 for ; Fri, 9 Mar 2007 12:07:09 -0400 (AST) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id 670E21C05C; Fri, 9 Mar 2007 17:07:05 +0100 (CET) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Fri, 9 Mar 2007 17:07:05 +0100 Message-ID: <45F18629.4000808@telefor.hu> Date: Fri, 09 Mar 2007 17:07:05 +0100 From: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Organization: Telefor kft. User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: Richard Huxton CC: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com> <45F180D2.5080201@telefor.hu> <45F182B7.30507@archonet.com> In-Reply-To: <45F182B7.30507@archonet.com> Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 09 Mar 2007 16:07:05.0209 (UTC) FILETIME=[FB157E90:01C76264] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.457 tagged_above=0 required=5 tests=AWL, BAYES_50, HTML_40_50, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200703/201 X-Sequence-Number: 23606 Richard Huxton wrote:
Re: [PERFORM] Deceiding which index to use

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 :-)

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

Zizi
From mezei.zoltan@telefor.hu Fri Mar 9 16:14:31 2007 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 617559FB94F for ; Fri, 9 Mar 2007 12:14:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 59352-07 for ; Fri, 9 Mar 2007 12:14:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.telefor.hu (mail.telefor.hu [193.68.53.97]) by postgresql.org (Postfix) with ESMTP id 794A79FB591 for ; Fri, 9 Mar 2007 12:14:21 -0400 (AST) Received: from telefor-server.int.telefor-new.hu (telefor-server.int.telefor-new.hu [10.0.20.100]) by mail.telefor.hu (Postfix) with ESMTP id F1E851C09F; Fri, 9 Mar 2007 17:14:15 +0100 (CET) Received: from [10.0.20.33] ([10.0.20.33]) by telefor-server.int.telefor-new.hu with Microsoft SMTPSVC(6.0.3790.1830); Fri, 9 Mar 2007 17:14:15 +0100 Message-ID: <45F187D7.1070705@telefor.hu> Date: Fri, 09 Mar 2007 17:14:15 +0100 From: =?ISO-8859-1?Q?Mezei_Zolt=E1n?= Organization: Telefor kft. User-Agent: Thunderbird 2.0b2 (Windows/20070116) MIME-Version: 1.0 To: =?ISO-8859-1?Q?Mezei_Zolt=E1n?= , Richard Huxton , pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com> <45F180D2.5080201@telefor.hu> <20070309160009.GC10875@alvh.no-ip.org> In-Reply-To: <20070309160009.GC10875@alvh.no-ip.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 09 Mar 2007 16:14:15.0782 (UTC) FILETIME=[FBB9B860:01C76265] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.226 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200703/202 X-Sequence-Number: 23607 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 From dev@archonet.com Fri Mar 9 16:17:53 2007 Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 838159FB94F for ; Fri, 9 Mar 2007 12:17:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 25075-01 for ; Fri, 9 Mar 2007 12:17:44 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 8569C9FBAE9 for ; Fri, 9 Mar 2007 12:17:44 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id E540B1433EC; Fri, 9 Mar 2007 16:17:43 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id C710127408E; Fri, 9 Mar 2007 16:17:43 +0000 (GMT) Message-ID: <45F188A7.3000409@archonet.com> Date: Fri, 09 Mar 2007 16:17:43 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: =?ISO-8859-1?Q?Mezei_Zolt=E1n?= Cc: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com> <45F180D2.5080201@telefor.hu> <20070309160009.GC10875@alvh.no-ip.org> <45F187D7.1070705@telefor.hu> In-Reply-To: <45F187D7.1070705@telefor.hu> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.2 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/203 X-Sequence-Number: 23608 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 From dev@archonet.com Fri Mar 9 16:24:37 2007 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id AE0FE9FBB83 for ; Fri, 9 Mar 2007 12:24:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 06515-07 for ; Fri, 9 Mar 2007 12:24:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 7224F9FBB48 for ; Fri, 9 Mar 2007 12:24:13 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 79E9B741E2; Fri, 9 Mar 2007 16:18:57 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 5B56E27401E; Fri, 9 Mar 2007 16:18:57 +0000 (GMT) Message-ID: <45F188F1.4010905@archonet.com> Date: Fri, 09 Mar 2007 16:18:57 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: =?UTF-8?B?TWV6ZWkgWm9sdMOhbg==?= Cc: pgsql-performance@postgresql.org Subject: Re: Deceiding which index to use References: <45F15F0F.8080403@telefor.hu> <45F1715E.1050000@archonet.com> <45F174CF.8010302@telefor.hu> <45F1775A.8030701@archonet.com> <45F179AB.7040000@telefor.hu> <45F17B70.60001@archonet.com> <45F180D2.5080201@telefor.hu> <45F182B7.30507@archonet.com> <45F18629.4000808@telefor.hu> In-Reply-To: <45F18629.4000808@telefor.hu> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.2 tagged_above=0 required=5 tests=AWL, BAYES_50, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200703/204 X-Sequence-Number: 23609 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