From e.ghanem@acteos.com Wed Jul  21 07:31:49 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id EC69363775B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 04:31:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 43644-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 07:31:41 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mailrelay7-out.ornis.com (mailrelay7-out.ornis.com
 [81.93.0.90])
	by mail.postgresql.org (Postfix) with ESMTP id 2FCEF63309C
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 04:31:41 -0300 (ADT)
Received: from fsecure2.ornis.com ([192.168.162.2])
	by mailrelay7-out.ornis.com (8.12.11/8.12.11) with ESMTP id
	o6L7Vddw004525
	for <pgsql-performance@postgresql.org>; Wed, 21 Jul 2010 09:31:39 +0200
Received: from unknown (HELO relay-ext.ornis.com) ([192.168.160.6])
	by mailgate402.ornis.com with ESMTP; 21 Jul 2010 09:31:39 +0200
Received: from [192.168.0.71] ([80.79.155.178])
	by relay-ext.ornis.com (8.12.9/8.12.3) with ESMTP id o6L7VGlu021448
	for <pgsql-performance@postgresql.org>; Wed, 21 Jul 2010 09:31:27 +0200
Message-ID: <4C46A23B.2090606@acteos.com>
Date: Wed, 21 Jul 2010 10:31:07 +0300
From: Elias Ghanem <e.ghanem@acteos.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Using more tha one index per table
Content-Type: multipart/alternative;
	boundary="------------030004050601090303030405"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201007/295
X-Sequence-Number: 39684

This is a multi-part message in MIME format.
--------------030004050601090303030405
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
I have a question concerning the uses of indexes in Postgresql.
I red that in PG a query can not use more than one index per table: "a 
query or data manipulation command can use at most one index per table".
Actually I found this a little weird and unfortunately I could not find 
further explanation with my Google searches. But the tests I made proved 
that this is true:

If we have table :

*create table test_index(col_1 integer, col_2 integer, col_3 integer, 
col_4 integer)*


and we have 2 indexes created on this table:

*create index idx_col_1 on test_index (col_1)*

*create index idx_col_2 on test_index (col_2)*

A query like : *select * from test_index where col_1 = 15 and col_2 = 
30* would never use both the indexes. The query plan is:

*"Index Scan using idx_col_2 on test_index (cost=0.00..8.27 rows=1 
width=16) (actual time=0.092..0.092 rows=0 loops=1)"*

*" Index Cond: (col_2 = 30)"*

*" Filter: (col_1 = 15)"*

*"Total runtime: 0.127 ms"*

The query will use *idx_col_2 *only and apply the other condition 
ignoring the other index(*idx_col_1*).


So please can you give some more details about this point. Is the above 
citation true or I misunderstood it?

A next step is what if a query made a join on two tables table1 and 
table2 (for ex: where table1.id = table2.id and table2.col_2 = 3 and 
table2.col_3 = 4)?
Will it use, for table2, the index of the join column (table2.id) only 
and neglect the indexes of the other two columns(col_2 and col_3) 
although they are present in the where clause.

Thanks for your response,

Elias


--------------030004050601090303030405
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=ISO-8859-1">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 3.2  (Win32)">
<style type="text/css">
	<!--
		@page { margin: 0.79in }
		P { margin-bottom: 0.08in }
	-->
	</style>
<p style="margin-bottom: 0in;">Hi,<br>
I have a question concerning the
uses of indexes in Postgresql.<br>
I red that in PG a query can not
use more than one index per table: "a query or data manipulation
command can use at most one index per table".<br>
Actually I
found this a little weird and unfortunately I could not find further
explanation with my Google searches. But the tests I made proved that
this is true:</p>
<p style="margin-bottom: 0in;">If we have table :</p>
<p style="margin-bottom: 0in;"><b>create table test_index(col_1
integer, col_2 integer, col_3 integer, col_4 integer)</b></p>
<br>
and we have 2 indexes created on this
table:
<p style="margin-bottom: 0in;"><b>create index idx_col_1 on test_index
(col_1)</b></p>
<p style="margin-bottom: 0in;"><b>create index idx_col_2 on test_index
(col_2)</b></p>
<p style="margin-bottom: 0in;">A query like : <b>select * from
test_index where col_1 = 15 and col_2 = 30</b> would never use both
the indexes. The query plan is:</p>
<p style="margin-bottom: 0in;"><b>"Index Scan using idx_col_2 on
test_index (cost=0.00..8.27 rows=1 width=16) (actual
time=0.092..0.092 rows=0 loops=1)"</b></p>
<p style="margin-bottom: 0in;"><b>" Index Cond: (col_2 = 30)"</b></p>
<p style="margin-bottom: 0in;"><b>" Filter: (col_1 = 15)"</b></p>
<p style="margin-bottom: 0in;"><b>"Total runtime: 0.127 ms"</b></p>
<p style="margin-bottom: 0in; font-weight: normal;">The query will use <b>idx_col_2
</b>only and apply the other condition ignoring the
other index(<b>idx_col_1</b>).</p>
<br>
So please can you
give some more details about this point. Is the above citation true
or I misunderstood it?<br>
<br>
A next step is
what if a query made a join on two tables table1 and table2 (for ex:
where table1.id = table2.id and table2.col_2 = 3 and table2.col_3 =
4)?<br>
Will it use, for
table2, the index of the join column (table2.id) only and neglect the
indexes of the other two columns(col_2 and col_3) although they are
present in the where clause.
<p style="margin-bottom: 0in; font-weight: normal;">Thanks for your
response,</p>
<p style="margin-bottom: 0in; font-weight: normal;">Elias</p>
</body>
</html>

--------------030004050601090303030405--




From andreas.kretschmer@schollglas.com Wed Jul  21 07:54:05 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 73728643034
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 04:54:05 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 81493-05-3
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 07:53:57 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.187])
	by mail.postgresql.org (Postfix) with ESMTP id 942E164300C
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 04:53:56 -0300 (ADT)
Received: from webserv.wug.de ([194.25.154.138])
	by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis)
	id 0MeMN5-1OPCKi169X-00Pa8P; Wed, 21 Jul 2010 09:53:55 +0200
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
	id 1ObU7l-0000C5-00
	for pgsql-performance@postgresql.org; Wed, 21 Jul 2010 09:53:53 +0200
Date: Wed, 21 Jul 2010 09:53:53 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
Message-ID: <20100721075353.GB20566@a-kretschmer.de>
References: <4C46A23B.2090606@acteos.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4C46A23B.2090606@acteos.com>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
User-Agent: Mutt/1.5.9i
X-Provags-ID: V02:K0:cZXZn+E2x/ovozJilC19mIuna77tT70bOP9a1Tm4RX3
	SNyriNM6Js6hsM5caDGR4Rnevn4nh8Zhhy6m/TqtwU1fi0aaDs
	fpTRsBZZnqmtzGuhklMsrjs1mCd1HCOt3rFtoXCsDAx0qG+ujv
	iZEdTV9e41MVRTiMPPwd8HkMKHiFTHlhJJSwcOKckov2FVuX7h
	BfmET/26/whCwVbDta7ig==
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.81 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	RCVD_IN_DNSWL_NONE=-0.0001, T_FRT_CONTACT=0.01
X-Spam-Level: 
X-Archive-Number: 201007/296
X-Sequence-Number: 39685

In response to Elias Ghanem :
> Hi,
> I have a question concerning the uses of indexes in Postgresql.
> I red that in PG a query can not use more than one index per table: "a query or
> data manipulation command can use at most one index per table".

That's not true, but it's true for MySQL, afaik.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99



From wultsch@gmail.com Wed Jul  21 07:58:48 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 5CEB76340D1
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 04:58:48 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 82550-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 07:58:40 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com
	[209.85.216.46])
	by mail.postgresql.org (Postfix) with ESMTP id 0FBD1633342
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 04:58:40 -0300 (ADT)
Received: by qwh6 with SMTP id 6so2372330qwh.19
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 00:58:40 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:mime-version:received:in-reply-to
	:references:from:date:message-id:subject:to:cc:content-type;
	bh=QCMsKHCAMLYKOWLCfCAbnr79xAeSDCDTHE17mG4pK7U=;
	b=M+YEdTgXSwm2lisJfIfUHc5FvbWjg2RmlKqTl8gaJUTNNfRf+ofO6A2NtOK0lgjjDn
	LCMJtFrm48lfNFqvFOxazBfjY3lezjSovJUcgVu72s8NaHagovkKzM44W1uGQ4e6kUnn
	+DUBaDFqMwRT9gFZYpeu3Kw5WWY0jxjqrx2Ug=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	b=aemaqdtk7LC5vGJZ0QOG/mmlt5IU1/qC4IfrM9H3ki2LTfn9s/O0AMk8Tnc/581IwM
	mBXMTIfJE13oIAglda/RpNOfkzOion0aR218YVgCZx/f5AkPVIuro6sDeuIn0LdkyMiu
	PgD7JZjFXR+LPulJm9qnHGjHft+Pez4V1mELs=
Received: by 10.224.3.19 with SMTP id 19mr286287qal.369.1279699118149;
 Wed, 21
	Jul 2010 00:58:38 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.43.146 with HTTP; Wed, 21 Jul 2010 00:58:16 -0700 (PDT)
In-Reply-To: <20100721075353.GB20566@a-kretschmer.de>
References: <4C46A23B.2090606@acteos.com>
	<20100721075353.GB20566@a-kretschmer.de>
From: Rob Wultsch <wultsch@gmail.com>
Date: Wed, 21 Jul 2010 00:58:16 -0700
Message-ID: <AANLkTilBKkfLTLOsiRyjY0MK8oRYqWxmNCx1PZhOMofO@mail.gmail.com>
Subject: Re: Using more tha one index per table
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=0015175cdd2a5c2834048be12b06
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.811 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001,
	T_FRT_CONTACT=0.01
X-Spam-Level: 
X-Archive-Number: 201007/297
X-Sequence-Number: 39686

--0015175cdd2a5c2834048be12b06
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Jul 21, 2010 at 12:53 AM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:

> In response to Elias Ghanem :
> > Hi,
> > I have a question concerning the uses of indexes in Postgresql.
> > I red that in PG a query can not use more than one index per table: "a
> query or
> > data manipulation command can use at most one index per table".
>
> That's not true, but it's true for MySQL, afaik.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

That is not true either, though MySQL is less good at using bitmap'ed
indexes. 5.0 can use "merge indexes",

-- 
Rob Wultsch
wultsch@gmail.com

--0015175cdd2a5c2834048be12b06
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">On Wed, Jul 21, 2010 at 12:53 AM, A. Kretschmer =
<span dir=3D"ltr">&lt;<a href=3D"mailto:andreas.kretschmer@schollglas.com">=
andreas.kretschmer@schollglas.com</a>&gt;</span> wrote:<br><blockquote clas=
s=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; border-left: 1px soli=
d rgb(204, 204, 204); padding-left: 1ex;">

In response to Elias Ghanem :<br>
<div class=3D"im">&gt; Hi,<br>
&gt; I have a question concerning the uses of indexes in Postgresql.<br>
&gt; I red that in PG a query can not use more than one index per table: &q=
uot;a query or<br>
&gt; data manipulation command can use at most one index per table&quot;.<b=
r>
<br>
</div>That&#39;s not true, but it&#39;s true for MySQL, afaik.<br>
<br>
<br>
Andreas<br>
--<br>
Andreas Kretschmer<br>
Kontakt: =A0Heynitz: 035242/47150, =A0 D1: 0160/7141639 (mehr: -&gt; Header=
)<br>
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 =A02EB0 389D 1DC2 3172 0C99<br>
<font color=3D"#888888"><br>
--<br>
Sent via pgsql-performance mailing list (<a href=3D"mailto:pgsql-performanc=
e@postgresql.org">pgsql-performance@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-performance" target=3D"=
_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br>
</font></blockquote></div><br>That is not true either, though MySQL is less=
 good at using bitmap&#39;ed indexes. 5.0 can use &quot;merge indexes&quot;=
,<br clear=3D"all"><br>-- <br>Rob Wultsch<br><a href=3D"mailto:wultsch@gmai=
l.com">wultsch@gmail.com</a><br>



--0015175cdd2a5c2834048be12b06--



From scott.marlowe@gmail.com Wed Jul  21 08:01:13 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 718DD633342
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 05:01:13 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 56887-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 08:01:02 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com
	[209.85.212.46])
	by mail.postgresql.org (Postfix) with ESMTP id F37BF632BF6
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 05:01:01 -0300 (ADT)
Received: by vws14 with SMTP id 14so1354290vws.19
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 01:01:00 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:received:in-reply-to
	:references:date:message-id:subject:from:to:cc:content-type;
	bh=lXgn0CN44MdUpSpJ6zxfsJ0urNgr7vec0N093FDzbzY=;
	b=au2VvNbMHX8P4cek0XrJoPvaRtvhYSlP5tXJQMiLz066iRO+8441Dqs1UnssLUA/oO
	W9C+PRRMz3HPWRMYTMhahZkjjAeHXXCy3HvZKiF+PFcY2Ghf3i3mbQo2lVhgYFXGB2No
	+uTMUkXwwrkgSXxJpal5oYp6Dk3PqFC7nkbug=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=Y24SVDFViHWBatSK/a7nyzwdZQTldi9+/US58kEBpZEJR4V8TmLBPW7cIhr0RZCs29
	2o+Ky06p4c+mTn3fudqsqxjXBZE9tQLDZK3KMWvA+cdFI57q6b4BpKutbrhpZtYoJddW
	VNVlCw1Tf6AUqvp9Smp9aBzT2ViK71Fk/FTIU=
MIME-Version: 1.0
Received: by 10.224.66.75 with SMTP id m11mr6678162qai.157.1279699260490;
 Wed,
	21 Jul 2010 01:01:00 -0700 (PDT)
Received: by 10.229.231.73 with HTTP; Wed, 21 Jul 2010 01:01:00 -0700 (PDT)
In-Reply-To: <4C46A23B.2090606@acteos.com>
References: <4C46A23B.2090606@acteos.com>
Date: Wed, 21 Jul 2010 02:01:00 -0600
Message-ID: <AANLkTildEz3XOdGGR5RDjikkyWTCPtXisBIqx1EjVJKr@mail.gmail.com>
Subject: Re: Using more tha one index per table
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Elias Ghanem <e.ghanem@acteos.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/298
X-Sequence-Number: 39687

On Wed, Jul 21, 2010 at 1:31 AM, Elias Ghanem <e.ghanem@acteos.com> wrote:
> Hi,
> I have a question concerning the uses of indexes in Postgresql.
> I red that in PG a query can not use more than one index per table: "a query
> or data manipulation command can use at most one index per table".
> Actually I found this a little weird and unfortunately I could not find
> further explanation with my Google searches. But the tests I made proved
> that this is true:
>
> If we have table :
>
> create table test_index(col_1 integer, col_2 integer, col_3 integer, col_4
> integer)
>
> and we have 2 indexes created on this table:
>
> create index idx_col_1 on test_index (col_1)
>
> create index idx_col_2 on test_index (col_2)
>
> A query like : select * from test_index where col_1 = 15 and col_2 = 30
> would never use both the indexes. The query plan is:
>
> "Index Scan using idx_col_2 on test_index (cost=0.00..8.27 rows=1 width=16)
> (actual time=0.092..0.092 rows=0 loops=1)"
>
> " Index Cond: (col_2 = 30)"
>
> " Filter: (col_1 = 15)"
>
> "Total runtime: 0.127 ms"
>
> The query will use idx_col_2 only and apply the other condition ignoring the
> other index(idx_col_1).
>
> So please can you give some more details about this point. Is the above
> citation true or I misunderstood it?

Well, it's not really a citation without a source, which you didn't
provide.  But it's definitely no longer true, and hasn't been for some
years now.  I think it was 8.0 or 8.1 that introduced bitmap index
scans.  Here's a sample query that uses on from one of my dbs at work.

explain select * from test_table where id1 between 3047964 and 1261382
and id2 between 443365 and 452479;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=2497.05..3535.69 rows=870 width=326)
   Recheck Cond: ((id1 >= 3047964) AND (id1 <= 1261382) AND (id2 >=
443365) AND (id2 <= 452479))
   ->  BitmapAnd  (cost=2497.05..2497.05 rows=870 width=0)
         ->  Bitmap Index Scan on test_table_pkey  (cost=0.00..181.67
rows=13573 width=0)
               Index Cond: ((id1 >= 3047964) AND (id1 <= 1261382))
         ->  Bitmap Index Scan on test_table_id2_idx
(cost=0.00..2314.70 rows=174076 width=0)
               Index Cond: ((id2 >= 443365) AND (id2 <= 452479))


> A next step is what if a query made a join on two tables table1 and table2
> (for ex: where table1.id = table2.id and table2.col_2 = 3 and table2.col_3 =
> 4)?
> Will it use, for table2, the index of the join column (table2.id) only and
> neglect the indexes of the other two columns(col_2 and col_3) although they
> are present in the where clause.

None of the behavior of the query planner is written in stone.  You'll
notice that up above the query planner has estimated costs for each
operation.  Pgsql's query planner will look at various options and
choose the cheapest, which may or my not be to use a bitmap index scan
on your query.

Explain select ... will show you the plan.

Explain analyze select ... will show you plan and the actual execution
of it, so you can compare what the query planner expected and what
really happened.  Note that explain analyze actually runs the query,
so explain analyze delete will actually delete things.  You can get
around this with a transaction:

begin;
explain analyze delete ... ;
rollback;



From scott.marlowe@gmail.com Wed Jul  21 08:03:44 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 5A579643143
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 05:03:44 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 60501-03-4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 08:03:36 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com
	[209.85.216.46])
	by mail.postgresql.org (Postfix) with ESMTP id 131EA64308D
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 05:03:29 -0300 (ADT)
Received: by qwh6 with SMTP id 6so2373754qwh.19
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 01:03:29 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:received:in-reply-to
	:references:date:message-id:subject:from:to:cc:content-type;
	bh=4tpnWGGfZ/51CKGSvSQA4pEvi9FemyAoDk5/DbWjnhk=;
	b=kXh9MsSnEWlyw18Yrf7xhGX1V/TFIq0zVjSopRkKkbYk0ltkK7V4U2hN/gRb4azt2p
	HUmJ9WnWepo7SKDYC/9z5P1g1q1pkyvDf/Y87fDwENNz6uIv6O6Lq6EhyWYMDU4reXbH
	KgR7sL9GjvkXzUQN28nUfLbko4X/y3FC5qmeE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=XdsLTItIPGizFmuCFyRVA36xHqpbqHnCiDaEo9X9o87GSkccSH6EHpt6XXDi4Mp0FJ
	ka2PNWlpR2zTDVAboTsxr3TCb6vd6TtCYrBogrrkO5pfT1jJYkBFYrCIOt4Wvz1C1ICK
	J7w/o0C68seTB4NJ+VY0XtjSD+dKypSe6uz/0=
MIME-Version: 1.0
Received: by 10.224.97.81 with SMTP id k17mr6102754qan.122.1279699408943;
 Wed,
	21 Jul 2010 01:03:28 -0700 (PDT)
Received: by 10.229.231.73 with HTTP; Wed, 21 Jul 2010 01:03:28 -0700 (PDT)
In-Reply-To: <AANLkTilBKkfLTLOsiRyjY0MK8oRYqWxmNCx1PZhOMofO@mail.gmail.com>
References: <4C46A23B.2090606@acteos.com>
	<20100721075353.GB20566@a-kretschmer.de>
	<AANLkTilBKkfLTLOsiRyjY0MK8oRYqWxmNCx1PZhOMofO@mail.gmail.com>
Date: Wed, 21 Jul 2010 02:03:28 -0600
Message-ID: <AANLkTilGwVkxsvBoPlJ4h25QrC4z7pqvBCMBqB_-ef5y@mail.gmail.com>
Subject: Re: Using more tha one index per table
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Rob Wultsch <wultsch@gmail.com>
Cc: "A. Kretschmer" <andreas.kretschmer@schollglas.com>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/299
X-Sequence-Number: 39688

On Wed, Jul 21, 2010 at 1:58 AM, Rob Wultsch <wultsch@gmail.com> wrote:
> On Wed, Jul 21, 2010 at 12:53 AM, A. Kretschmer
> <andreas.kretschmer@schollglas.com> wrote:
>>
>> In response to Elias Ghanem :
>> > Hi,
>> > I have a question concerning the uses of indexes in Postgresql.
>> > I red that in PG a query can not use more than one index per table: "a
>> > query or
>> > data manipulation command can use at most one index per table".
>>
>> That's not true, but it's true for MySQL, afaik.
>>
>
> That is not true either, though MySQL is less good at using bitmap'ed
> indexes. 5.0 can use "merge indexes",

Yeah, the biggest problem MySQL has is that it's got a pretty
simplistic query planner so it often makes poor choices.

Note that PostgreSQL on the other hand, has a much smarter query
planner.  So it usually makes better choices.  But when it makes a
wrong one, it can be a doozie.  Luckily, reported strange behavior in
the query planner is usually fixed pretty quickly.



From andy@squeakycode.net Wed Jul  21 14:14:14 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 840F9631ADA
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 11:14:14 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 90846-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 14:14:05 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from marajade.localdomain (vcs5.camavision.com [63.228.164.252])
	by mail.postgresql.org (Postfix) with ESMTP id 374B762DBDD
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 11:14:06 -0300 (ADT)
Received: from [192.168.10.84] (unknown [192.168.10.84])
	(Authenticated sender: andy)
	by marajade.localdomain (Postfix) with ESMTPA id BAD2140C15;
	Wed, 21 Jul 2010 09:14:03 -0500 (CDT)
Message-ID: <4C4700B3.2000505@squeakycode.net>
Date: Wed, 21 Jul 2010 09:14:11 -0500
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.4) Gecko/20100608 Lightning/1.0b2 Thunderbird/3.1
MIME-Version: 1.0
To: Elias Ghanem <e.ghanem@acteos.com>, pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com>
In-Reply-To: <4C46A23B.2090606@acteos.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8
X-Spam-Level: 
X-Archive-Number: 201007/300
X-Sequence-Number: 39689

On 7/21/2010 2:31 AM, Elias Ghanem wrote:
> Hi,
> I have a question concerning the uses of indexes in Postgresql.
> I red that in PG a query can not use more than one index per table: "a
> query or data manipulation command can use at most one index per table".
> Actually I found this a little weird and unfortunately I could not find
> further explanation with my Google searches. But the tests I made proved
> that this is true:
>
> If we have table :
>
> *create table test_index(col_1 integer, col_2 integer, col_3 integer,
> col_4 integer)*
>
>
> and we have 2 indexes created on this table:
>
> *create index idx_col_1 on test_index (col_1)*
>
> *create index idx_col_2 on test_index (col_2)*
>
> A query like : *select * from test_index where col_1 = 15 and col_2 =
> 30* would never use both the indexes. The query plan is:
>
> *"Index Scan using idx_col_2 on test_index (cost=0.00..8.27 rows=1
> width=16) (actual time=0.092..0.092 rows=0 loops=1)"*
>
> *" Index Cond: (col_2 = 30)"*
>
> *" Filter: (col_1 = 15)"*
>
> *"Total runtime: 0.127 ms"*
>
> The query will use *idx_col_2 *only and apply the other condition
> ignoring the other index(*idx_col_1*).
>
>
> So please can you give some more details about this point. Is the above
> citation true or I misunderstood it?
>
> A next step is what if a query made a join on two tables table1 and
> table2 (for ex: where table1.id = table2.id and table2.col_2 = 3 and
> table2.col_3 = 4)?
> Will it use, for table2, the index of the join column (table2.id) only
> and neglect the indexes of the other two columns(col_2 and col_3)
> although they are present in the where clause.
>
> Thanks for your response,
>
> Elias
>

As others have said, it will use more than one index.  The question you 
may have though, is why didnt it?

Its because an index isn't always faster.  The answer to both your 
questions (does it get used, and how about in a join) comes down to 
selectivity.  If an index can drastically cut down the number of rows 
then it'll be used, otherwise its faster to scan for the ones you need.

In your first example:
select * from test_index where col_1 = 15 and col_2 = 30

the planner will use whatever index has the test selectivity.  If 100's 
of rows have col_1 = 15, but only 5 rows have col_2 = 30, then its much 
faster to pull out the 5 rows with col_2 = 30 and just scan them for 
col_1 = 15.

Lets say both are highly selective (there are only a few rows each). 
Again its not going to be faster to use both indexes:

read the col_1 index for 15
fetch 5 rows from db.
read the col2_ index for 30
fetch different 5 rows from db
scan/bitmap the 10 rows for the both col_1 and col_2 conditions.

vs:
read col_1 index for 15
fetch 5 rows from db.
scan 5 rows for col_2 condition

The join case is exactly the same.  If the index can be used to reduce 
the resultset, or make individual row lookups faster, then it'll be used.

I have some description tables, like:  (id integer, descr text)
with maybe 100 rows in it.  PG never uses the unique index on id, it 
always table scans it... because its faster.

-Andy



From greg@2ndquadrant.com Wed Jul  21 15:00:11 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id D489C633126
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 12:00:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 24803-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 15:00:03 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.10])
	by mail.postgresql.org (Postfix) with ESMTP id E3B1C632998
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 12:00:02 -0300 (ADT)
Received: from [192.168.1.3] (static-71-179-240-8.bltmmd.fios.verizon.net
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis)
	id 0M6gB2-1OwidD1w84-00wSnK; Wed, 21 Jul 2010 16:59:56 +0200
Message-ID: <4C470B69.6080304@2ndquadrant.com>
Date: Wed, 21 Jul 2010 10:59:53 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Elias Ghanem <e.ghanem@acteos.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com>
In-Reply-To: <4C46A23B.2090606@acteos.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V02:K0:akLN+C585s4K8Sls2oACg1Q+Rsce/MSowwZ2Rv7mh7c
	wBSi7KnZOsnnOdGDY3Wq1O+k21cvmqEKbHflfqNi0YHYGK9wQi
	odB4ruZYfOv2NxQiybzt/59gJ8NmduyJhBfA+jLQNO0Gpiij6i
	GUYYkm5z8Nqb2g62foD0e6Q5jWHWyihH4eM2yImYRdr3mWw3LV
	quhvgku8ey/SvfrPkR9sHlYduRSL+Zx0pLkl6uV01s=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5
	tests=BAYES_20=-0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/302
X-Sequence-Number: 39691

Elias Ghanem wrote:
>
> I red that in PG a query can not use more than one index per table: "a 
> query or data manipulation command can use at most one index per table".
>

You'll find that at the very end of 
http://www.postgresql.org/docs/7.4/static/indexes.html and 
http://www.postgresql.org/docs/8.0/static/indexes.html ; try 
http://www.postgresql.org/docs/8.1/static/indexes.html instead and 
you'll discover that text has been removed because it was no longer true 
as of this version.  If you find yourself at a PostgreSQL documentation 
page, often the search engines link to an older version with outdated 
information just because those have had more time accumulate links to 
them.  A useful trick to know is that if you replace the version number 
with "current", you'll get to the latest version most of the time 
(sometimes the name of the page is changed between versions, too, but 
this isn't that frequent).

So for this example, 
http://www.postgresql.org/docs/current/static/indexes.html will take you 
to the documentation for 8.4, which is the latest released version.

As for your example, you can't test optimizer behavior with trivial 
tables.  The overhead of using the index isn't zero, and it will often 
be deemed excessive for a small example.  So for this:

*"Index Scan using idx_col_2 on test_index (cost=0.00..8.27 rows=1 
width=16) (actual time=0.092..0.092 rows=0 loops=1)"*

*" Index Cond: (col_2 = 30)"*

*" Filter: (col_1 = 15)"*



Once it uses the one index, it only expects one row to be returned, at 
which point it has no need to use a second index.  Faster to just look 
at that row and use some CPU time to determine if it matches.  Using the 
second index for that instead would require some disk access to look up 
things in it, which will take longer than running the filter.  That's 
why the second one isn't used.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us




From craig@postnewspapers.com.au Thu Jul  22 00:48:13 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 7C6A8632E29
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 21:48:13 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 90699-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 00:48:04 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from www.postnewspapers.com.au (www.postnewspapers.com.au
	[202.4.227.11])
	by mail.postgresql.org (Postfix) with ESMTP id 244A4632A24
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 21:48:06 -0300 (ADT)
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by www.postnewspapers.com.au (Postfix) with ESMTP id 31DEB9ECD;
	Thu, 22 Jul 2010 08:48:02 +0800 (WST)
Received: from localhost (localhost [127.0.0.1])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id B367CAEB12;
	Thu, 22 Jul 2010 08:45:13 +0800 (WST)
X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au
Received: from mail.postnewspapers.com.au ([127.0.0.1])
	by localhost (mail.postnewspapers.com.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with LMTP id N5KjhcAKk8CN; Thu, 22 Jul 2010 08:45:08 +0800 (WST)
Received: from [10.1.1.6] (124-168-255-56.dyn.iinet.net.au [124.168.255.56])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client CN "Craig Ringer",
	Issuer "POST Certificate Authority" (verified OK))
	by mail.postnewspapers.com.au (Postfix) with ESMTPSA id 78F2BAE463;
	Thu, 22 Jul 2010 08:45:08 +0800 (WST)
Message-ID: <4C47953D.9090603@postnewspapers.com.au>
Date: Thu, 22 Jul 2010 08:47:57 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9) Gecko/20100423 Lightning/1.0b1 Thunderbird/3.0.4
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.com>
CC: Elias Ghanem <e.ghanem@acteos.com>, pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
In-Reply-To: <4C470B69.6080304@2ndquadrant.com>
X-Enigmail-Version: 1.0.1
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201007/306
X-Sequence-Number: 39695

On 21/07/10 22:59, Greg Smith wrote:

>  A useful trick to know is that if you replace the version number
> with "current", you'll get to the latest version most of the time
> (sometimes the name of the page is changed between versions, too, but
> this isn't that frequent).

The docs pages could perhaps benefit from an auto-generated note saying:

"The current version of Pg is 8.4. This documentation is for version
8.2. Click [here] for documentation on the current version."

... or something to that effect. It'd be a nice (and more user-friendly)
alternative to url twiddling when searches reveal docs for an old
version, and might help push the /current/ pages up in search rank too.

--
Craig Ringer



From craig_james@emolecules.com Thu Jul  22 01:03:28 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id E4232632EEC
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 22:03:28 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 03203-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 01:03:20 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-gx0-f174.google.com (mail-gx0-f174.google.com
	[209.85.161.174])
	by mail.postgresql.org (Postfix) with ESMTP id CCDAA632663
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 22:03:21 -0300 (ADT)
Received: by gxk23 with SMTP id 23so4060597gxk.19
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 18:03:19 -0700 (PDT)
Received: by 10.224.69.16 with SMTP id x16mr788122qai.284.1279760599596;
	Wed, 21 Jul 2010 18:03:19 -0700 (PDT)
Received: from Craig-Jamess-MacBook-Pro.local
	(wsip-70-167-124-126.sd.sd.cox.net [70.167.124.126])
	by mx.google.com with ESMTPS id
	fb41sm30101389qcb.15.2010.07.21.18.03.18
	(version=SSLv3 cipher=RC4-MD5); Wed, 21 Jul 2010 18:03:18 -0700 (PDT)
Message-ID: <4C4798D5.7030700@emolecules.com>
Date: Wed, 21 Jul 2010 18:03:17 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
In-Reply-To: <4C47953D.9090603@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201007/307
X-Sequence-Number: 39696

On 7/21/10 5:47 PM, Craig Ringer wrote:
> On 21/07/10 22:59, Greg Smith wrote:
>
>>   A useful trick to know is that if you replace the version number
>> with "current", you'll get to the latest version most of the time
>> (sometimes the name of the page is changed between versions, too, but
>> this isn't that frequent).
>
> The docs pages could perhaps benefit from an auto-generated note saying:
>
> "The current version of Pg is 8.4. This documentation is for version
> 8.2. Click [here] for documentation on the current version."
>
> ... or something to that effect. It'd be a nice (and more user-friendly)
> alternative to url twiddling when searches reveal docs for an old
> version, and might help push the /current/ pages up in search rank too.

In addition, why not use symlinks so that the current version is simply called "current", as in

    http://www.postgresql.org/docs/current/static/sql-insert.html

If you google for "postgres insert", you get this:

   http://www.postgresql.org/docs/8.1/static/sql-insert.html

The problem is that Google ranks pages based on inbound links, so older versions of Postgres *always* come up before the latest version in page ranking.  By using "current" and encouraging people to link to that, we could quickly change the Google pagerank so that a search for Postgres would turn up the most-recent version of documentation.

Craig



From greg@2ndquadrant.com Thu Jul  22 01:47:57 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 3CE56632663
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 22:47:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 24487-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 01:47:48 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.10])
	by mail.postgresql.org (Postfix) with ESMTP id 10B1563103A
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 22:47:50 -0300 (ADT)
Received: from [192.168.1.5] (static-71-179-240-8.bltmmd.fios.verizon.net
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mreu2) with ESMTP (Nemesis)
	id 0LiZ4a-1PBveZ0aWy-00d3cm; Thu, 22 Jul 2010 03:47:47 +0200
Message-ID: <4C47A341.7010905@2ndquadrant.com>
Date: Wed, 21 Jul 2010 21:47:45 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Craig James <craig_james@emolecules.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
In-Reply-To: <4C4798D5.7030700@emolecules.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V02:K0:0TrHbLiyRnZH2LGWrOxPOi6fjJOXCEKdfRv2EqWh6LA
	o+kmT4RWrRWklh8tywXFxc65S7hJB3AbgSIBKzy5cj+wi1NaQj
	CHrb5nMOCIEqerIuJM9lRxKDEWv7wFod8o3EhDMO2t+4E+koOl
	tvDkoWCM3do3ED7Mihqd+PIqqbznNADQYGPjcRxzKFtyUyDZRI
	pAmwS0cmQvjdBx2ttFzWpiPdlEwYWhH9ovVTPEYPiA=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/308
X-Sequence-Number: 39697

Craig James wrote:
> By using "current" and encouraging people to link to that, we could 
> quickly change the Google pagerank so that a search for Postgres would 
> turn up the most-recent version of documentation.

How do you propose to encourage people to do that?  If I had a good 
answer to that question, I'd already be executing on it.  I've made a 
habit of doing that when writing articles on the wiki, which hopefully 
themselves become popular and then elevate those links (all of the ones 
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server for 
example point to current).  I don't know how to target "people who link 
to the PostgreSQL manual" beyond raising awareness of the issue 
periodically on these lists, like I did on this thread.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us




From steve@blighty.com Thu Jul  22 02:00:49 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id C238A632B66
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 23:00:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 02218-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 02:00:42 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from m.wordtothewise.com (b.wordtothewise.com [208.187.80.136])
	by mail.postgresql.org (Postfix) with ESMTP id 77B15632946
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 23:00:42 -0300 (ADT)
Received: from platter.wordtothewise.com (184.wordtothewise.com
	[208.187.80.184]) (using TLSv1 with cipher AES128-SHA (128/128 bits))
	(No client certificate requested) (Authenticated sender: steve)
	by m.wordtothewise.com (Postfix) with ESMTPSA id 0CC452E196
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 19:03:45 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=wordtothewise.com;
	s=1.wttw; t=1279764225;
	bh=p1yCENtFAECfYB05nSr80q9lNKE8eKrCJM3WZVAfaeg=;
	h=Content-Type:Mime-Version:Subject:From:In-Reply-To:Date:
	Content-Transfer-Encoding:Message-Id:References:To;
	b=EmkG1wZ4Z71faroDUw5/Qj7d6uNOCOBUsgvfWDmPY99aZ/yOaov/sRDj+TL37h/2J
	DTZROrcZnlpAqorwX2tbbONayd0XRb8joryOFgA9Pjx4kEbiuyhJtgnaUo/nPRdyYf
	EZfATHDPi+wjnerZsIieuBQ3AFykFMPL/yarOrRI=
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1081)
Subject: Re: Using more tha one index per table
From: Steve Atkins <steve@blighty.com>
In-Reply-To: <4C47A341.7010905@2ndquadrant.com>
Date: Wed, 21 Jul 2010 19:00:39 -0700
Content-Transfer-Encoding: quoted-printable
Message-Id: <91A47C2E-2802-4719-BCC6-B82A125DCB70@blighty.com>
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<4C47A341.7010905@2ndquadrant.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
X-Mailer: Apple Mail (2.1081)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201007/309
X-Sequence-Number: 39698


On Jul 21, 2010, at 6:47 PM, Greg Smith wrote:

> Craig James wrote:
>> By using "current" and encouraging people to link to that, we could =
quickly change the Google pagerank so that a search for Postgres would =
turn up the most-recent version of documentation.
>=20
> How do you propose to encourage people to do that?  If I had a good =
answer to that question, I'd already be executing on it. =20

When people link to a page, they link to the URL they copy and paste out =
of the browser address bar.

If http://postgresql.org/docs/9.0/* were to 302 redirect to =
http://postgresql.org/docs/current/* while 9.0 is the current release =
(and similarly for 9.1 and so on) I suspect we'd find many more links to =
current and fewer links to specific versions after a year or two.

> I've made a habit of doing that when writing articles on the wiki, =
which hopefully themselves become popular and then elevate those links =
(all of the ones =
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server for =
example point to current).  I don't know how to target "people who link =
to the PostgreSQL manual" beyond raising awareness of the issue =
periodically on these lists, like I did on this thread.

Cheers,
  Steve




From greg@2ndquadrant.com Thu Jul  22 02:28:02 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 3ED5F632946
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 23:28:02 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 11806-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 02:27:55 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.171])
	by mail.postgresql.org (Postfix) with ESMTP id D0C2363282C
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 23:27:54 -0300 (ADT)
Received: from [192.168.1.3] (static-71-179-240-8.bltmmd.fios.verizon.net
	[71.179.240.8])
	by mrelayeu.kundenserver.de (node=mrbap2) with ESMTP (Nemesis)
	id 0LmwP6-1PGKSn2kWq-00h7t9; Thu, 22 Jul 2010 04:27:50 +0200
Message-ID: <4C47ACA3.7030202@2ndquadrant.com>
Date: Wed, 21 Jul 2010 22:27:47 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Steve Atkins <steve@blighty.com>
CC: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<4C47A341.7010905@2ndquadrant.com>
	<91A47C2E-2802-4719-BCC6-B82A125DCB70@blighty.com>
In-Reply-To: <91A47C2E-2802-4719-BCC6-B82A125DCB70@blighty.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V02:K0:iJIFQMlaCNFQQbf0LHGBckbNqN9CNmZJEPLBSTXzXKx
	xMc3dQwcRlfplmzwFkweyvaFfEDdJmC47VFmxipxE/AfHatdeA
	yGWcojFJIh7a/tJtLcNw4lf6AXHyWJcdvIQsPEFpUOZurJMV75
	yZyOC/x0N7SYLoc3+vO8u+Q3uR3mj23i1iB1C9v9h1u9PrfK/k
	Ilu31fY2sScPxO51oEMvxNvj5j2WeqF/jNFhd8a0ho=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.5 tagged_above=-10 required=5 tests=BAYES_05=-0.5,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/310
X-Sequence-Number: 39699

Steve Atkins wrote:
> If http://postgresql.org/docs/9.0/* were to 302 redirect to http://postgresql.org/docs/current/* while 9.0 is the current release (and similarly for 9.1 and so on) I suspect we'd find many more links to current and fewer links to specific versions after a year or two.
>   

True, but this would leave people with no way to bookmark a permanent 
link to whatever is the current version, which will represent a 
regression for how some people want the site to work.  Also, this and 
the idea to add a "this is an old version" note to each old page will 
end up increasing work for the already overloaded web team managing the 
site.  Neither are unreasonable ideas, there's just some subtle bits to 
making either happen that would need to be worked out, and I don't know 
who would have time to work through everything involved.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us




From steve@blighty.com Thu Jul  22 02:55:52 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 3089563264E
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 21 Jul 2010 23:55:52 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 88039-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 02:55:42 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from m.wordtothewise.com (b.wordtothewise.com [208.187.80.136])
	by mail.postgresql.org (Postfix) with ESMTP id E974B62DBEC
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 23:55:44 -0300 (ADT)
Received: from platter.wordtothewise.com (184.wordtothewise.com
	[208.187.80.184]) (using TLSv1 with cipher AES128-SHA (128/128 bits))
	(No client certificate requested) (Authenticated sender: steve)
	by m.wordtothewise.com (Postfix) with ESMTPSA id B21B52DE93
	for <pgsql-performance@postgresql.org>;
	Wed, 21 Jul 2010 19:58:48 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=wordtothewise.com;
	s=1.wttw; t=1279767528;
	bh=CuDbMORoaLyDXGjAJ7MgyffG+F6hzNYowPDBzTLdvLY=;
	h=Content-Type:Mime-Version:Subject:From:In-Reply-To:Date:
	Content-Transfer-Encoding:Message-Id:References:To;
	b=a6VFSWVKqUzp1hZkYlwI4Bty4YDCuNN57ar7fCb+RjHwycHF1rSCo16zFeKEq1DJe
	mlG796oMYtowRMgexED/w9zFFa/p1lS3Hey6pn480FqxV3oO6idrLvSTafMzpW0SII
	+sH3P+VvWQ03u+bTJ1x8TZ1t0GNS1RTkat/HcRsg=
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1081)
Subject: Re: Using more tha one index per table
From: Steve Atkins <steve@blighty.com>
In-Reply-To: <4C47ACA3.7030202@2ndquadrant.com>
Date: Wed, 21 Jul 2010 19:55:42 -0700
Content-Transfer-Encoding: quoted-printable
Message-Id: <A0ABF05B-DB0A-45C9-B4F0-8C57FC561870@blighty.com>
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<4C47A341.7010905@2ndquadrant.com>
	<91A47C2E-2802-4719-BCC6-B82A125DCB70@blighty.com>
	<4C47ACA3.7030202@2ndquadrant.com>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
X-Mailer: Apple Mail (2.1081)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201007/311
X-Sequence-Number: 39700


On Jul 21, 2010, at 7:27 PM, Greg Smith wrote:

> Steve Atkins wrote:
>> If http://postgresql.org/docs/9.0/* were to 302 redirect to =
http://postgresql.org/docs/current/* while 9.0 is the current release =
(and similarly for 9.1 and so on) I suspect we'd find many more links to =
current and fewer links to specific versions after a year or two.
>> =20
>=20
> True, but this would leave people with no way to bookmark a permanent =
link to whatever is the current version, which will represent a =
regression for how some people want the site to work.

Well, they'd still be able to link to the specific version with =
../9.0/.. and have that link to the version 9.0 docs forever, just not =
as easily as a copy/paste. That's the whole point, though, to make the =
wanted behaviour easier than the  unwanted.

>  Also, this and the idea to add a "this is an old version" note to =
each old page will end up increasing work for the already overloaded web =
team managing the site.  Neither are unreasonable ideas, there's just =
some subtle bits to making either happen that would need to be worked =
out, and I don't know who would have time to work through everything =
involved.

Yup. I'm not convinced it's a great idea either - but it's about the =
only thing that'd get people to link to ../current/.. by default.

Cheers,
  Steve




From dev@archonet.com Thu Jul  22 08:35:56 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 357F66330C7
	for <pgsql-docs-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 05:35:56 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 34824-08; Thu, 22 Jul 2010 08:35:48 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from relay.pcl-ipout02.plus.net (relay.pcl-ipout02.plus.net
	[212.159.7.100])
	by mail.postgresql.org (Postfix) with ESMTP id 56FA263402F;
	Thu, 22 Jul 2010 05:35:47 -0300 (ADT)
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: ApsEANifR0zUnw4T/2dsb2JhbACgZ8BphTYE
Received: from outmx06.plus.net (HELO outmx04.plus.net) ([212.159.14.19])
	by relay.pcl-ipout02.plus.net with ESMTP; 22 Jul 2010 09:35:45 +0100
Received: from 84-51-143-99.archon037.adsl.metronet.co.uk ([84.51.143.99]
	helo=server3.office.archonet.com)
	by outmx04.plus.net with esmtp (Exim) id 1ObrFp-0005nf-6l;
	Thu, 22 Jul 2010 09:35:45 +0100
Received: from [192.168.0.36] (dell36outside.office.archonet.com
	[192.168.0.36])
	by server3.office.archonet.com (Postfix) with ESMTPA id B4EDA27408D;
	Thu, 22 Jul 2010 09:35:44 +0100 (BST)
Message-ID: <4C4802E0.50704@archonet.com>
Date: Thu, 22 Jul 2010 09:35:44 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.9pre) Gecko/20100217 Lightning/1.0b1 Shredder/3.0.3pre
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.com>
CC: Steve Atkins <steve@blighty.com>,
	PostgreSQL Performance <pgsql-performance@postgresql.org>,
	pgsql-docs@postgresql.org
Subject: Re: [PERFORM] Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<4C47A341.7010905@2ndquadrant.com>
	<91A47C2E-2802-4719-BCC6-B82A125DCB70@blighty.com>
	<4C47ACA3.7030202@2ndquadrant.com>
In-Reply-To: <4C47ACA3.7030202@2ndquadrant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.1 tagged_above=-5 required=5 tests=BAYES_50=0.8,
	RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201007/142
X-Sequence-Number: 5798

On 22/07/10 03:27, Greg Smith wrote:
> Steve Atkins wrote:
>> If http://postgresql.org/docs/9.0/* were to 302 redirect to
>> http://postgresql.org/docs/current/* while 9.0 is the current release
>> (and similarly for 9.1 and so on) I suspect we'd find many more links
>> to current and fewer links to specific versions after a year or two.
>
> True, but this would leave people with no way to bookmark a permanent
> link to whatever is the current version, which will represent a
> regression for how some people want the site to work.

Having a quick look at the website, a simple change might be to have a 
large "CURRENT MANUALS" link above all the versioned links. That should 
help substantially.

-- 
   Richard Huxton
   Archonet Ltd



From wultsch@gmail.com Thu Jul  22 09:09:57 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 4512D63459A
	for <pgsql-docs-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 06:09:57 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 54752-02; Thu, 22 Jul 2010 09:09:49 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-gy0-f174.google.com (mail-gy0-f174.google.com
	[209.85.160.174])
	by mail.postgresql.org (Postfix) with ESMTP id E5CD0634535;
	Thu, 22 Jul 2010 06:09:48 -0300 (ADT)
Received: by gyg10 with SMTP id 10so600689gyg.19
	for <multiple recipients>; Thu, 22 Jul 2010 02:09:45 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:received:mime-version:received:in-reply-to
	:references:from:date:message-id:subject:to:cc:content-type;
	bh=BXhfzBsjsoRyRj8n2Jm3H5hr0Yd6QogquxOx+5L+pC0=;
	b=RTJAtMMJoXB87HlChgdkzzY/Io1hDNlGRhAJvjjvJ8fg15f7SFILYIiw4RoLlqqpSC
	0CdW7gJBNbzjD9c6Z95v9oHw8vSJBBt5UV0pNxRt+BrfFRIi4O5k0jMQOFO2V6+B3u+s
	vqEEPPGTO2GDAF0/hmsatYe+x6M+A/IW85Jfc=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	b=Y2rksZm0SK8CYgXyGCSy3B4Jc7lMZQmALf7fvqls6csujyUCowo0v9+/bad7sAMgFD
	FTZXYLLqm9/0+Ru58W0fhKeYcqO3rvkQ/U7EjAtZneZqXdUODCOpFqpLiO/uPviEoNgK
	y2qUQllWM+Ys1HqjT6SC9vxnC+PTpVAs258As=
Received: by 10.224.34.208 with SMTP id m16mr1050350qad.285.1279789782105;
	Thu, 22 Jul 2010 02:09:42 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.43.146 with HTTP; Thu, 22 Jul 2010 02:09:21 -0700 (PDT)
In-Reply-To: <4C4802E0.50704@archonet.com>
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<4C47A341.7010905@2ndquadrant.com>
	<91A47C2E-2802-4719-BCC6-B82A125DCB70@blighty.com>
	<4C47ACA3.7030202@2ndquadrant.com> <4C4802E0.50704@archonet.com>
From: Rob Wultsch <wultsch@gmail.com>
Date: Thu, 22 Jul 2010 02:09:21 -0700
Message-ID: <AANLkTimtOB80HRcCG1tiD3MPph1ngj_c8Jz9zTKKvgYd@mail.gmail.com>
Subject: Re: [PERFORM] Using more tha one index per table
To: Richard Huxton <dev@archonet.com>
Cc: Greg Smith <greg@2ndquadrant.com>, Steve Atkins <steve@blighty.com>,
	PostgreSQL Performance <pgsql-performance@postgresql.org>,
	pgsql-docs@postgresql.org
Content-Type: multipart/alternative; boundary=00c09fa2180b597fe7048bf64704
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9,
	HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201007/145
X-Sequence-Number: 5801

--00c09fa2180b597fe7048bf64704
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Jul 22, 2010 at 1:35 AM, Richard Huxton <dev@archonet.com> wrote:

> On 22/07/10 03:27, Greg Smith wrote:
>
>> Steve Atkins wrote:
>>
>>> If http://postgresql.org/docs/9.0/* were to 302 redirect to
>>> http://postgresql.org/docs/current/* while 9.0 is the current release
>>> (and similarly for 9.1 and so on) I suspect we'd find many more links
>>> to current and fewer links to specific versions after a year or two.
>>>
>>
>> True, but this would leave people with no way to bookmark a permanent
>> link to whatever is the current version, which will represent a
>> regression for how some people want the site to work.
>>
>
> Having a quick look at the website, a simple change might be to have a
> large "CURRENT MANUALS" link above all the versioned links. That should help
> substantially.
>
> --
>  Richard Huxton
>  Archonet Ltd
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

I suggested a few weeks ago adding a drop down menu for other version of the
manual for a page. I have not had time to write a patch, but I think it is
something that MySQL does better that pg.

As an example take a look at the page on select for MySQL:
http://dev.mysql.com/doc/refman/5.1/en/select.html .

If you want a earlier or later version they are easily accessible via a link
on the left.


-- 
Rob Wultsch
wultsch@gmail.com

--00c09fa2180b597fe7048bf64704
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">On Thu, Jul 22, 2010 at 1:35 AM, Richard=
 Huxton <span dir=3D"ltr">&lt;<a href=3D"mailto:dev@archonet.com">dev@archo=
net.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D=
"margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padd=
ing-left: 1ex;">

<div class=3D"im">On 22/07/10 03:27, Greg Smith wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; borde=
r-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Steve Atkins wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin: 0pt 0pt 0pt 0.8ex; borde=
r-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
If <a href=3D"http://postgresql.org/docs/9.0/*" target=3D"_blank">http://po=
stgresql.org/docs/9.0/*</a> were to 302 redirect to<br>
<a href=3D"http://postgresql.org/docs/current/*" target=3D"_blank">http://p=
ostgresql.org/docs/current/*</a> while 9.0 is the current release<br>
(and similarly for 9.1 and so on) I suspect we&#39;d find many more links<b=
r>
to current and fewer links to specific versions after a year or two.<br>
</blockquote>
<br>
True, but this would leave people with no way to bookmark a permanent<br>
link to whatever is the current version, which will represent a<br>
regression for how some people want the site to work.<br>
</blockquote>
<br></div>
Having a quick look at the website, a simple change might be to have a larg=
e &quot;CURRENT MANUALS&quot; link above all the versioned links. That shou=
ld help substantially.<br><font color=3D"#888888">
<br>
-- <br>
 =A0Richard Huxton<br>
 =A0Archonet Ltd</font><div><div></div><div class=3D"h5"><br>
<br>
-- <br>
Sent via pgsql-performance mailing list (<a href=3D"mailto:pgsql-performanc=
e@postgresql.org" target=3D"_blank">pgsql-performance@postgresql.org</a>)<b=
r>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-performance" target=3D"=
_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br>
</div></div></blockquote></div><br>I suggested a few weeks ago adding a dro=
p down menu for other version of the manual for a page. I have not had time=
 to write a patch, but I think it is something that MySQL does better that =
pg.<br clear=3D"all">

<br>As an example take a look at the page on select for MySQL: <a href=3D"h=
ttp://dev.mysql.com/doc/refman/5.1/en/select.html">http://dev.mysql.com/doc=
/refman/5.1/en/select.html</a> .<br><br>If you want a earlier or later vers=
ion they are easily accessible via a link on the left.<br>

<br><br>-- <br>Rob Wultsch<br><a href=3D"mailto:wultsch@gmail.com">wultsch@=
gmail.com</a><br>

--00c09fa2180b597fe7048bf64704--



From craig_james@emolecules.com Thu Jul  22 16:57:23 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 033BA632AC1
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 13:57:23 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 19605-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 22 Jul 2010 16:57:12 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ww0-f50.google.com (mail-ww0-f50.google.com
 [74.125.82.50])
	by mail.postgresql.org (Postfix) with ESMTP id DA02763250C
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Jul 2010 13:57:12 -0300 (ADT)
Received: by wwe15 with SMTP id 15so3032557wwe.19
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Jul 2010 09:57:11 -0700 (PDT)
Received: by 10.227.157.138 with SMTP id b10mr830252wbx.226.1279817830980;
	Thu, 22 Jul 2010 09:57:10 -0700 (PDT)
Received: from Craig-Jamess-MacBook-Pro.local
	(wsip-70-167-124-126.sd.sd.cox.net [70.167.124.126])
	by mx.google.com with ESMTPS id n17sm4193332weq.30.2010.07.22.09.57.08
	(version=SSLv3 cipher=RC4-MD5); Thu, 22 Jul 2010 09:57:10 -0700 (PDT)
Message-ID: <4C487862.4030604@emolecules.com>
Date: Thu, 22 Jul 2010 09:57:06 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<4C47A341.7010905@2ndquadrant.com>
In-Reply-To: <4C47A341.7010905@2ndquadrant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/322
X-Sequence-Number: 39711

On 7/21/10 6:47 PM, Greg Smith wrote:
> Craig James wrote:
>> By using "current" and encouraging people to link to that, we could
>> quickly change the Google pagerank so that a search for Postgres would
>> turn up the most-recent version of documentation.
>
> How do you propose to encourage people to do that? If I had a good
> answer to that question, I'd already be executing on it. I've made a
> habit of doing that when writing articles on the wiki, which hopefully
> themselves become popular and then elevate those links (all of the ones
> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server for
> example point to current). I don't know how to target "people who link
> to the PostgreSQL manual" beyond raising awareness of the issue
> periodically on these lists, like I did on this thread.

You don't have to get everyone to do it.  Just get more people to link to "current" than to other versions and you win in the Google ranking.

Start by sending an announcement to every PG mailing list.  You'd probably get a couple thousand right away, which by itself might do the trick.

Once "current" reaches the top of the Google ranking, it will cascade: People searching for Postgres documentation will find "current" first, and will post links to it, which will further reinforce its popularity.

There will always be people who link to older versions, but since the versions change frequently and "current" lasts forever, its ranking will constantly build until it ultimately wins.

There's no downside to it.  It's easy to do.  The other ideas (like putting "out of date" disclaimers and such into older versions) might also be useful, but might be a lot of work for just a little more gain.  Creating a "current" link is simple and in the long run will be very effective.  The sooner it starts, the sooner it will gain traction.

Craig



From foo@meisterderspiele.de Fri Jul  23 09:25:24 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 621D3632CDB
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 23 Jul 2010 06:25:24 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 95666-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 23 Jul 2010 09:25:15 +0000 (UTC)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by mail.postgresql.org (Postfix) with ESMTP id 9A89E632AEE
	for <pgsql-performance@postgresql.org>;
	Fri, 23 Jul 2010 06:25:16 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.14.4/8.14.4) with ESMTP id o6N9PB9h074273
	for <pgsql-performance@postgresql.org>;
	Fri, 23 Jul 2010 06:25:11 -0300 (ADT)
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.14.4/8.14.4/Submit) id o6N9MFll066546
	for pgsql-performance@postgresql.org;
	Fri, 23 Jul 2010 06:22:15 -0300 (ADT) (envelope-from news)
From: =?ISO-8859-1?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
X-Newsgroups: pgsql.performance
Subject: Re: Using more tha one index per table
Date: Fri, 23 Jul 2010 11:22:18 +0200
Organization: A noiseless patient Spider
Lines: 40
Message-ID: <i2bn0a$12p$1@news.eternal-september.org>
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Fri, 23 Jul 2010 09:22:18 +0000 (UTC)
Injection-Info: mx02.eternal-september.org;
	posting-host="AnkPUomeHXvuN7UdHvIkog";
	logging-data="1113"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX1+KKXHWx2pWo7gsP9k7xbd4"
User-Agent: Mozilla/5.0 (X11; U; Linux i686; de;
	rv:1.8.1.24) Gecko/20100228 Thunderbird/2.0.0.24 Mnenhy/0.7.5.0
In-Reply-To: <4C4798D5.7030700@emolecules.com>
Cancel-Lock: sha1:HmfuLNIATqcrAwco3MDhim6uaAk=
To: pgsql-performance@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.79 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201007/335
X-Sequence-Number: 39724

Craig James schrieb:

>>>   A useful trick to know is that if you replace the version number
>>> with "current", you'll get to the latest version most of the time
>>> (sometimes the name of the page is changed between versions, too, but
>>> this isn't that frequent).
>>
>> The docs pages could perhaps benefit from an auto-generated note saying:
>>
>> "The current version of Pg is 8.4. This documentation is for version
>> 8.2. Click [here] for documentation on the current version."
>>
>> ... or something to that effect. It'd be a nice (and more user-friendly)
>> alternative to url twiddling when searches reveal docs for an old
>> version, and might help push the /current/ pages up in search rank too.
> 
> In addition, why not use symlinks so that the current version is simply 
> called "current", as in
> 
>    http://www.postgresql.org/docs/current/static/sql-insert.html
> 
> If you google for "postgres insert", you get this:
> 
>   http://www.postgresql.org/docs/8.1/static/sql-insert.html
> 
> The problem is that Google ranks pages based on inbound links, so older 
> versions of Postgres *always* come up before the latest version in page 
> ranking.  

Since 2009 you can deal with this by defining the canonical-version. 
(http://googlewebmastercentral.blogspot.com/2009/02/specify-your-canonical.html)

Greetings from Germany,
Torsten

-- 
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.



From craig_james@emolecules.com Fri Jul  23 21:47:34 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id E2CA063250C
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 23 Jul 2010 18:47:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 67938-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Fri, 23 Jul 2010 21:47:26 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com
	[74.125.83.174])
	by mail.postgresql.org (Postfix) with ESMTP id 6D7C8632A5B
	for <pgsql-performance@postgresql.org>;
	Fri, 23 Jul 2010 18:47:26 -0300 (ADT)
Received: by pvc7 with SMTP id 7so3678884pvc.19
	for <pgsql-performance@postgresql.org>;
	Fri, 23 Jul 2010 14:47:25 -0700 (PDT)
Received: by 10.142.194.15 with SMTP id r15mr4930177wff.31.1279921645056;
	Fri, 23 Jul 2010 14:47:25 -0700 (PDT)
Received: from Craig-Jamess-MacBook-Pro.local
	(wsip-70-167-124-126.sd.sd.cox.net [70.167.124.126])
	by mx.google.com with ESMTPS id c23sm738059rvf.12.2010.07.23.14.47.23
	(version=SSLv3 cipher=RC4-MD5); Fri, 23 Jul 2010 14:47:24 -0700 (PDT)
Message-ID: <4C4A0DEB.9060501@emolecules.com>
Date: Fri, 23 Jul 2010 14:47:23 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<i2bn0a$12p$1@news.eternal-september.org>
In-Reply-To: <i2bn0a$12p$1@news.eternal-september.org>
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=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9,
	RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/340
X-Sequence-Number: 39729

On 7/23/10 2:22 AM, Torsten Zühlsdorff wrote:
> Craig James schrieb:
>
>>>> A useful trick to know is that if you replace the version number
>>>> with "current", you'll get to the latest version most of the time
>>>> (sometimes the name of the page is changed between versions, too, but
>>>> this isn't that frequent).
>>>
>>> The docs pages could perhaps benefit from an auto-generated note saying:
>>>
>>> "The current version of Pg is 8.4. This documentation is for version
>>> 8.2. Click [here] for documentation on the current version."
>>>
>>> ... or something to that effect. It'd be a nice (and more user-friendly)
>>> alternative to url twiddling when searches reveal docs for an old
>>> version, and might help push the /current/ pages up in search rank too.
>>
>> In addition, why not use symlinks so that the current version is
>> simply called "current", as in
>>
>> http://www.postgresql.org/docs/current/static/sql-insert.html
>>
>> If you google for "postgres insert", you get this:
>>
>> http://www.postgresql.org/docs/8.1/static/sql-insert.html
>>
>> The problem is that Google ranks pages based on inbound links, so
>> older versions of Postgres *always* come up before the latest version
>> in page ranking.
>
> Since 2009 you can deal with this by defining the canonical-version.
> (http://googlewebmastercentral.blogspot.com/2009/02/specify-your-canonical.html)

This is a really cool feature, but it's not what we need.  The "canonical" refers to the URL, not the web page.  It's only supposed to be used if you have multiple URLs that are actually the *same* page; the "canonical" URL tells Google "use only this URL for this page."

But in our case, the Postgres manuals for each release have different URLs *and* different content, so the "canonical URL" isn't the right solution.

Craig

> Greetings from Germany,
> Torsten
>




From foo@meisterderspiele.de Sat Jul  24 13:25:40 2010
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 680C5632525
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat, 24 Jul 2010 10:25:40 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 81350-02
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat, 24 Jul 2010 13:25:33 +0000 (UTC)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by mail.postgresql.org (Postfix) with ESMTP id 4B46C62DBEC
	for <pgsql-performance@postgresql.org>;
	Sat, 24 Jul 2010 10:25:33 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72])
	by news.hub.org (8.14.4/8.14.4) with ESMTP id o6ODPQKw072619
	for <pgsql-performance@postgresql.org>;
	Sat, 24 Jul 2010 10:25:26 -0300 (ADT)
	(envelope-from news@news.hub.org)
Received: (from news@localhost)
	by news.hub.org (8.14.4/8.14.4/Submit) id o6OCvYsm097419
	for pgsql-performance@postgresql.org;
	Sat, 24 Jul 2010 09:57:34 -0300 (ADT) (envelope-from news)
From: =?ISO-8859-1?Q?Torsten_Z=FChlsdorff?= <foo@meisterderspiele.de>
X-Newsgroups: pgsql.performance
Subject: Re: Using more tha one index per table
Date: Sat, 24 Jul 2010 14:57:38 +0200
Organization: A noiseless patient Spider
Lines: 45
Message-ID: <i2eo03$27m$1@news.eternal-september.org>
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<i2bn0a$12p$1@news.eternal-september.org>
	<4C4A0DEB.9060501@emolecules.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Sat, 24 Jul 2010 12:57:39 +0000 (UTC)
Injection-Info: mx02.eternal-september.org;
	posting-host="rRqMbIKLzWUo7bSjVFNXYQ";
	logging-data="2294"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX19vusu4U+607f3j1IyYhLTZ"
User-Agent: Thunderbird 2.0.0.21 (X11/20090330)
In-Reply-To: <4C4A0DEB.9060501@emolecules.com>
Cancel-Lock: sha1:mBXyJbQPNeoBynEFt3TZB9m7l+Q=
To: pgsql-performance@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.79 tagged_above=-10 required=5 tests=BAYES_50=0.8,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201007/353
X-Sequence-Number: 39742

Craig James schrieb:

>>> The problem is that Google ranks pages based on inbound links, so
>>> older versions of Postgres *always* come up before the latest version
>>> in page ranking.
>>
>> Since 2009 you can deal with this by defining the canonical-version.
>> (http://googlewebmastercentral.blogspot.com/2009/02/specify-your-canonical.html) 
>>
> 
> This is a really cool feature, but it's not what we need.  The 
> "canonical" refers to the URL, not the web page.  It's only supposed to 
> be used if you have multiple URLs that are actually the *same* page; the 
> "canonical" URL tells Google "use only this URL for this page."
> 
> But in our case, the Postgres manuals for each release have different 
> URLs *and* different content, so the "canonical URL" isn't the right 
> solution.

This is true, but the content is allowed to change "a little". Of course 
their is no percentage of allowed changes. But it can be quite much. 
I've used this feature for some clients, which push their content into 
very different websites and it does work.
Most of the content of the documentation doesn't change much between the 
releases. In most cases the canonical will work the way i suggest.

In case of big changes even the recommandation of using a "current" 
version won't work. Its true that Google ranks pages based on inbound 
links. But there are more than 200 other factores, which influence the 
rankings. Most people do not know, that changing most of a sites content 
makes the inbound links for a long time useless. After big changes in 
the documentation the "current" entry will be droped for some monthes 
and the old entries will appear. But note, that every single site of the 
documentation is ranked for itself. From my experience i would expect 
the canonical-version with better results, than the current-version.

But the canonical is not the best solution in my opinion. I often edit 
the urls of some documentations, because i need it for a special 
postgresql version. The documentation clearly misses a version-switch. 
Combined with an big note, that the current displayed documentation is 
not the one of the current postgresql-version, this will be the best 
compromiss in my opinion.

Greetings from Germany,
Torsten



From craig_james@emolecules.com Sat Jul  24 14:59:47 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 2F3D46323C4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat, 24 Jul 2010 11:59:47 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 32923-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat, 24 Jul 2010 14:59:40 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail-px0-f174.google.com (mail-px0-f174.google.com
	[209.85.212.174])
	by mail.postgresql.org (Postfix) with ESMTP id E534262DBEF
	for <pgsql-performance@postgresql.org>;
	Sat, 24 Jul 2010 11:59:39 -0300 (ADT)
Received: by pxi14 with SMTP id 14so4057642pxi.19
	for <pgsql-performance@postgresql.org>;
	Sat, 24 Jul 2010 07:59:38 -0700 (PDT)
Received: by 10.142.147.7 with SMTP id u7mr5911047wfd.221.1279983578338;
	Sat, 24 Jul 2010 07:59:38 -0700 (PDT)
Received: from Craig-Jamess-MacBook-Pro.local
	(cpe-76-176-215-35.san.res.rr.com [76.176.215.35])
	by mx.google.com with ESMTPS id z1sm1692100wfd.15.2010.07.24.07.59.37
	(version=SSLv3 cipher=RC4-MD5); Sat, 24 Jul 2010 07:59:37 -0700 (PDT)
Message-ID: <4C4AFFD8.8000004@emolecules.com>
Date: Sat, 24 Jul 2010 07:59:36 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.1.11) Gecko/20100711 Thunderbird/3.0.6
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com>
	<i2bn0a$12p$1@news.eternal-september.org>
	<4C4A0DEB.9060501@emolecules.com>
	<i2eo03$27m$1@news.eternal-september.org>
In-Reply-To: <i2eo03$27m$1@news.eternal-september.org>
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.001 tagged_above=-10 required=5
	tests=BAYES_20=-0.001, RCVD_IN_DNSWL_NONE=-0.0001
X-Spam-Level: 
X-Archive-Number: 201007/354
X-Sequence-Number: 39743

On 7/24/10 5:57 AM, Torsten Zühlsdorff wrote:
> Craig James schrieb:
>
>>>> The problem is that Google ranks pages based on inbound links, so
>>>> older versions of Postgres *always* come up before the latest version
>>>> in page ranking.
>>>
>>> Since 2009 you can deal with this by defining the canonical-version.
>>> (http://googlewebmastercentral.blogspot.com/2009/02/specify-your-canonical.html)
>>>
>>
>> This is a really cool feature, but it's not what we need. The
>> "canonical" refers to the URL, not the web page. It's only supposed to
>> be used if you have multiple URLs that are actually the *same* page;
>> the "canonical" URL tells Google "use only this URL for this page."
>>
>> But in our case, the Postgres manuals for each release have different
>> URLs *and* different content, so the "canonical URL" isn't the right
>> solution.
>
> This is true, but the content is allowed to change "a little". Of course
> their is no percentage of allowed changes. But it can be quite much.
> I've used this feature for some clients, which push their content into
> very different websites and it does work.
> Most of the content of the documentation doesn't change much between the
> releases. In most cases the canonical will work the way i suggest.
>
> In case of big changes even the recommandation of using a "current"
> version won't work. Its true that Google ranks pages based on inbound
> links. But there are more than 200 other factores, which influence the
> rankings. Most people do not know, that changing most of a sites content
> makes the inbound links for a long time useless. After big changes in
> the documentation the "current" entry will be droped for some monthes
> and the old entries will appear. But note, that every single site of the
> documentation is ranked for itself. From my experience i would expect
> the canonical-version with better results, than the current-version.
>
> But the canonical is not the best solution in my opinion. I often edit
> the urls of some documentations, because i need it for a special
> postgresql version. The documentation clearly misses a version-switch.
> Combined with an big note, that the current displayed documentation is
> not the one of the current postgresql-version, this will be the best
> compromiss in my opinion.

Here's an idea: Use a "current" URL, plus a JavaScript embedded in every page that compares its own URL to the "current" URL and, if it doesn't match, does a "document.write()" indicating how to find the most-current version.

That would solve three problems:

   1. There would be a "current" version that people could link to.
   2. If someone found an old version, they would know it and could
      instantly be directed to the current version.
   3. It wouldn't be any burden on the web site maintainers, because
      the JavaScript wouldn't have to be changed.

Craig



From dfontaine@hi-media.com Sun Jul  25 08:29:37 2010
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 88D9C632D09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 25 Jul 2010 05:29:37 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 64757-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun, 25 Jul 2010 08:29:29 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp1.hi-media-techno.com (smtp1.hi-media-techno.com
	[195.158.241.85])
	by mail.postgresql.org (Postfix) with ESMTP id 4EE15632730
	for <pgsql-performance@postgresql.org>;
	Sun, 25 Jul 2010 05:29:30 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id 4A3EE93B91;
	Sun, 25 Jul 2010 10:29:28 +0200 (CEST)
Received: from smtp1.hi-media-techno.com ([127.0.0.1])
	by localhost (smtp1.backbone.hi-media-techno.com [127.0.0.1])
	(amavisd-new, port 10024)
	with ESMTP id oxlGHNHY6i4j; Sun, 25 Jul 2010 10:29:26 +0200 (CEST)
Received: from mail1.hi-media-techno.com (unknown [192.168.27.17])
	by smtp1.hi-media-techno.com (Postfix) with ESMTP id B2FC993BDB;
	Sun, 25 Jul 2010 10:29:26 +0200 (CEST)
Received: from DimIMac.local (89-158-241-175.rev.dartybox.com
 [89.158.241.175])
	by mail1.hi-media-techno.com (Postfix) with ESMTPSA id 28EE26840B;
	Sun, 25 Jul 2010 10:29:26 +0200 (CEST)
Received: by DimIMac.local (Postfix, from userid 501)
	id 36110BACAEB; Sun, 25 Jul 2010 10:29:26 +0200 (CEST)
From: Dimitri Fontaine <dfontaine@hi-media.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Craig James <craig_james@emolecules.com>, pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
Organization: Hi-Media
References: <4C46A23B.2090606@acteos.com> <4C470B69.6080304@2ndquadrant.com>
	<4C47953D.9090603@postnewspapers.com.au>
	<4C4798D5.7030700@emolecules.com> <4C47A341.7010905@2ndquadrant.com>
User-Mail-Address: dfontaine@hi-media.com
Date: Sun, 25 Jul 2010 10:29:26 +0200
In-Reply-To: <4C47A341.7010905@2ndquadrant.com> (Greg Smith's message of "Wed,
	21 Jul 2010 21:47:45 -0400")
Message-ID: <m24ofonevt.fsf@hi-media.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1.90 (darwin)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201007/365
X-Sequence-Number: 39754

Greg Smith <greg@2ndquadrant.com> writes:
> Craig James wrote:
>> By using "current" and encouraging people to link to that, we could
>> quickly change the Google pagerank so that a search for Postgres would
>> turn up the most-recent version of documentation.
>
> How do you propose to encourage people to do that?  

What about adding version information in huge letters in the top blue
bar, with all versions available in lower letters than what you're
looking at, and with current version nicely highlighted (color,
underlining, subtitle, whatever, we'd have to find a visual hints).

In other words, make it so big that you don't have to read the page
content to realise what version it is you're looking at.  Maybe we would
need to have this information stay visible on the page at the same place
when you scroll, too.

Regards,
-- 
dim