FAQ

Using more tha one index per table

Elias Ghanem
Jul 21, 2010 at 7:31 am
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
reply

Search Discussions

20 responses

  • Andreas Kretschmer at Jul 21, 2010 at 7:54 am

    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
  • Rob Wultsch at Jul 21, 2010 at 7:58 am

    On Wed, Jul 21, 2010 at 12:53 AM, A. Kretschmer 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
  • Scott Marlowe at Jul 21, 2010 at 8:03 am

    On Wed, Jul 21, 2010 at 1:58 AM, Rob Wultsch wrote:
    On Wed, Jul 21, 2010 at 12:53 AM, A. Kretschmer
    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.
  • Scott Marlowe at Jul 21, 2010 at 8:01 am

    On Wed, Jul 21, 2010 at 1: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?
    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;
  • Andy Colson at Jul 21, 2010 at 2:14 pm

    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
  • Greg Smith at Jul 21, 2010 at 3:00 pm

    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
  • Craig Ringer at Jul 22, 2010 at 12:48 am

    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
  • Craig James at Jul 22, 2010 at 1:03 am

    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
  • Greg Smith at Jul 22, 2010 at 1:47 am

    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
  • Steve Atkins at Jul 22, 2010 at 2:00 am

    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.
    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.
    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
  • Greg Smith at Jul 22, 2010 at 2:28 am

    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
  • Steve Atkins at Jul 22, 2010 at 2:55 am

    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.
    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
  • Richard Huxton at Jul 22, 2010 at 8:35 am

    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
  • Rob Wultsch at Jul 22, 2010 at 9:09 am

    On Thu, Jul 22, 2010 at 1:35 AM, Richard Huxton 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
  • Craig James at Jul 22, 2010 at 4:57 pm

    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
  • Dimitri Fontaine at Jul 25, 2010 at 8:29 am

    Greg Smith 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
  • Torsten Zühlsdorff at Jul 23, 2010 at 9:25 am

    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.
  • Craig James at Jul 23, 2010 at 9:47 pm

    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
  • Torsten Zühlsdorff at Jul 24, 2010 at 1:25 pm

    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
  • Craig James at Jul 24, 2010 at 2:59 pm

    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

Related Discussions