pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDeceiding which index to use
13+ messages / 3 participants
[nested] [flat]
* Deceiding which index to use
@ 2007-03-09 13:20 Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Mezei Zoltán @ 2007-03-09 13:20 UTC (permalink / raw)
To: pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="-1"><font face="Tahoma">Hi!<br>
<br>
I have two tables with some indices on them:<br>
<br>
CREATE TABLE subscriber<br>
(<br>
id serial NOT NULL,<br>
anumber character varying(32) NOT NULL,<br>
CONSTRAINT subscriber_pk PRIMARY KEY (id)<br>
) <br>
<br>
CREATE INDEX anumber_idx_numeric<br>
ON subscriber<br>
USING btree<br>
(anumber::numeric);<br>
<br>
CREATE TABLE output_message_log<br>
(<br>
id serial NOT NULL,<br>
subscriber_id integer NOT NULL,<br>
crd timestamp without time zone NOT NULL DEFAULT now(),<br>
CONSTRAINT output_message_log_pk PRIMARY KEY (id),<br>
CONSTRAINT subscriber_fk FOREIGN KEY (subscriber_id)<br>
REFERENCES subscriber (id) MATCH SIMPLE<br>
ON UPDATE NO ACTION ON DELETE NO ACTION,<br>
) <br>
<br>
CREATE INDEX crd_idx<br>
ON output_message_log<br>
USING btree<br>
(crd);<br>
<br>
CREATE INDEX subscriber_id_idx<br>
ON output_message_log<br>
USING btree<br>
(subscriber_id);<br>
<br>
I would like to run a query like this one:<br>
<br>
select l.id<br>
from output_message_log l join subscriber s on l.subscriber_id = s.id <br>
where s.anumber::numeric = 5555555555<br>
order by l.crd desc<br>
limit 41<br>
offset 20<br>
<br>
The thing I do not understand is why postgresql wants to use crd_idx:<br>
<br>
"Limit (cost=4848.58..14788.18 rows=41 width=12) (actual
time=7277.115..8583.814 rows=41 loops=1)"<br>
" -> Nested Loop (cost=0.00..1195418.42 rows=4931 width=12)
(actual time=92.083..8583.713 rows=61 loops=1)"<br>
" -> 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)"<br>
" -> 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)"<br>
" Index Cond: ("outer".subscriber_id = s.id)"<br>
" Filter: ((anumber)::numeric = 36308504669::numeric)"<br>
"Total runtime: 8584.016 ms"<br>
<br>
I would like postgresql to use </font></font><font size="-1"><font
face="Tahoma">subscriber_id_idx which resulst in a far less execution
time on this database.<br>
<br>
I tried to lower random_page_cost, but that didn't help as an index is
already used, just not the "good" one.<br>
<br>
Could you please comment on this issue and suggest some possible
soulutions?<br>
<br>
Thanks,<br>
<br>
Zizi<br>
</font></font><font size="-1"><font face="Tahoma"><br>
</font></font>
</body>
</html>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
@ 2007-03-09 14:38 ` Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2007-03-09 14:38 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
@ 2007-03-09 14:53 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Mezei Zoltán @ 2007-03-09 14:53 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<small><font face="Tahoma">Richard Huxton wrote:<br>
> Mezei Zoltán wrote:<br>
> Q1. Why are you storing a numeric in a varchar?<br>
<br>
Because it's not always numeric info. :/<br>
<br>
> Q2. How many unique values does anumber have? And how many rows in<br>
> subscriber?<br>
<br>
About 10k distinct anumbers and 20k rows. Nothing special...<br>
<br>
> Q3. What happens if you create the index on plain (anumber) and
then<br>
> test against '555555555'?<br>
<br>
Nothing, everything is the same - the problem lies on the other table's
index usage, using this index is fine.<br>
<br>
Zizi</font><font face="Tahoma"></font><br>
</small>
</body>
</html>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
@ 2007-03-09 15:03 ` Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2007-03-09 15:03 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
@ 2007-03-09 15:13 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Mezei Zoltán @ 2007-03-09 15:13 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Richard Huxton wrote:
<blockquote cite="mid:45F1775A.8030701@archonet.com" type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="MS Exchange Server version 6.5.7226.0">
<title>Re: [PERFORM] Deceiding which index to use</title>
<!-- Converted from text/plain format -->
<p><font size="2">And does the planner know that?<br>
SELECT * FROM pg_stats WHERE tablename='subscriber' AND
attname='anumber';<br>
It's the n_distinct you're interested in, and perhaps most_common_freqs.</font><br>
</p>
</blockquote>
<small>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?</small><br>
<blockquote cite="mid:45F1775A.8030701@archonet.com" type="cite">
<p><font size="2">> Nothing, everything is the same - the problem
lies on the other table's index<br>
> usage, using this index is fine.<br>
<br>
The planner has to guess how many matches it will have for<br>
subscriber=5555555. Based on that choice, it will either:<br>
a. Do the join, then find the highest crd values (sort)<br>
b. Scan the crd values backwards and then join<br>
It's chosen (b) because it's estimating the numbers of matches<br>
incorrectly. I'm wondering whether the system can't see through your<br>
function-call (the cast to numeric) to determine how many matches it's<br>
going to get for any given value.<br>
</font></p>
</blockquote>
<small>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.</small><br>
<blockquote cite="mid:45F1775A.8030701@archonet.com" type="cite">
<p><font size="2">If the system can't be persuaded into getting its
estimates more<br>
accurate, it might be worth trying an index on (subscriber_id,crd) and<br>
dropping the index on (crd) - if that's reasonable for your query
patterns.<br>
</font></p>
</blockquote>
<small>I'll try that one if the negative n_distinct value can be a
correct one :-)<br>
<br>
Zizi</small><br>
</body>
</html>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
@ 2007-03-09 15:21 ` Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2007-03-09 15:21 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; +Cc: pgsql-performance
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
<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.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
@ 2007-03-09 15:44 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:52 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 16:00 ` Re: Deceiding which index to use Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 13+ messages in thread
From: Mezei Zoltán @ 2007-03-09 15:44 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Richard Huxton wrote:
<blockquote cite="mid:45F17B70.60001@archonet.com" type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="MS Exchange Server version 6.5.7226.0">
<title>Re: [PERFORM] Deceiding which index to use</title>
<!-- Converted from text/plain format -->
<p><font size="2">OK - so the next place to look is the distribution
of values for<br>
subscriber_id on the output_message_log. Does that have some subscribers<br>
with many rows and lots with hardly any?</font></p>
</blockquote>
<small>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...</small><br>
<blockquote cite="mid:45F17B70.60001@archonet.com" type="cite">
<p><font size="2">If so, you might need to<br>
increase the stats on that column:<br>
<br>
ALTER TABLE output_message_log ALTER COLUMN subscriber_id SET STATISTICS<br>
<num>;<br>
ANALYSE output_message_log (subscriber_id);<br>
<br>
The <num> defaults to 10, but can be set as high as 1000. You
want to<br>
try and capture the "big" subscribers.<br>
</font></p>
</blockquote>
<small>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.<br>
<br>
Zizi<br>
</small>
</body>
</html>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
@ 2007-03-09 15:52 ` Richard Huxton <dev@archonet.com>
2007-03-09 16:07 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
1 sibling, 1 reply; 13+ messages in thread
From: Richard Huxton @ 2007-03-09 15:52 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; +Cc: pgsql-performance
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
> <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.
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:52 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
@ 2007-03-09 16:07 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 16:18 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Mezei Zoltán @ 2007-03-09 16:07 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<small>Richard Huxton wrote:</small>
<blockquote cite="mid:45F182B7.30507@archonet.com" type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="MS Exchange Server version 6.5.7226.0">
<title>Re: [PERFORM] Deceiding which index to use</title>
<!-- Converted from text/plain format -->
<p><font size="2">There is a cost to increasing the stats values,
otherwise it'd already<br>
be set at 1000. In your case I'm not sure if 100-200 vs 8-9 messages is<br>
enough to skew things. Only one way to find out...<br>
</font></p>
</blockquote>
<small>Well, I tried. The situation is:<br>
<br>
- when I look for a subscriber which the planner thinks to have more
messages --> it uses the index on crd<br>
- when I look for a subscriber which the planner thinks to have less
messages --> it uses the index on subscriber_id<br>
<br>
I think that's OK, even if it still don't do what I really would like
to see it do :-)<br>
<br>
Thanks for all your help, maybe I know a little bit more about
poostgresql than before.<br>
<br>
Zizi</small><br>
</body>
</html>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:52 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 16:07 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
@ 2007-03-09 16:18 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 13+ messages in thread
From: Richard Huxton @ 2007-03-09 16:18 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
@ 2007-03-09 16:00 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-03-09 16:14 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
1 sibling, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2007-03-09 16:00 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
Mezei Zoltán wrote:
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> Richard Huxton wrote:
> <blockquote cite="mid:45F17B70.60001@archonet.com" type="cite">
> <meta http-equiv="Content-Type" content="text/html; ">
> <meta name="Generator" content="MS Exchange Server version 6.5.7226.0">
> <title>Re: [PERFORM] Deceiding which index to use</title>
> <!-- Converted from text/plain format -->
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 16:00 ` Re: Deceiding which index to use Alvaro Herrera <alvherre@commandprompt.com>
@ 2007-03-09 16:14 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 16:17 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 13+ messages in thread
From: Mezei Zoltán @ 2007-03-09 16:14 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; Richard Huxton <dev@archonet.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Deceiding which index to use
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Re: Deceiding which index to use Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 16:00 ` Re: Deceiding which index to use Alvaro Herrera <alvherre@commandprompt.com>
2007-03-09 16:14 ` Re: Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
@ 2007-03-09 16:17 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 13+ messages in thread
From: Richard Huxton @ 2007-03-09 16:17 UTC (permalink / raw)
To: Mezei Zoltán <mezei.zoltan@telefor.hu>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-03-09 16:18 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-09 13:20 Deceiding which index to use Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 14:38 ` Richard Huxton <dev@archonet.com>
2007-03-09 14:53 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:03 ` Richard Huxton <dev@archonet.com>
2007-03-09 15:13 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:21 ` Richard Huxton <dev@archonet.com>
2007-03-09 15:44 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 15:52 ` Richard Huxton <dev@archonet.com>
2007-03-09 16:07 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 16:18 ` Richard Huxton <dev@archonet.com>
2007-03-09 16:00 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-03-09 16:14 ` Mezei Zoltán <mezei.zoltan@telefor.hu>
2007-03-09 16:17 ` Richard Huxton <dev@archonet.com>
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