Using more tha one index per table

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


Andreas

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.

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

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;

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

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.

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

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.

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.
Cheers,
Steve

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.

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

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.

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.

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

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,

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

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

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

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