From: Josh Berkus <josh@agliodbs.com>
To: Daniel Crisan <d.crisan@ibcp.fr>
Cc: PostgreSQL <pgsql-performance@postgresql.org>
Subject: Re: multi-column index
Date: Wed, 16 Mar 2005 10:09:24 -0800
Message-ID: <200503161009.24713.josh@agliodbs.com> (raw)
In-Reply-To: <42385A1B.9090305@ibcp.fr>
References: <42385A1B.9090305@ibcp.fr>
Daniel,
> Table "public.descriptionprodftdiclnk"
What is this, German? ;-)
> explain analyze select * from descriptionprodftdiclnk where idword=44;
> QUERY PLAN
> ---------------------------------------------------------------------------
>---------------------------------------------------- Seq Scan on
> descriptionprodftdiclnk (cost=0.00..4788.14 rows=44388 width=8) (actual
> time=87.582..168.041 rows=43792 loops=1)
> Filter: (idword = 44)
> Total runtime: 195.339 ms
> (3 rows)
> explain analyze select * from descriptionprodftdiclnk where idword=44;
>
> QUERY PLAN
> ---------------------------------------------------------------------------
>----------------------------------------------------------------------------
>------------ Index Scan using descriptionprodftdiclnk_pkey on
> descriptionprodftdiclnk (cost=0.00..36720.39 rows=44388 width=8)
> (actual time=0.205..73.489 rows=43792 loops=1)
> Index Cond: (idword = 44)
> Total runtime: 100.564 ms
> (3 rows)
> create index ix_tempIndex on descriptionprodftdiclnk(idword);
> CREATE INDEX
> explain analyze select * from descriptionprodftdiclnk where idword=44;
> QUERY
> PLAN
> ---------------------------------------------------------------------------
>---------------------------------------------------------------------- Index
> Scan using ix_tempindex on descriptionprodftdiclnk
> (cost=0.00..916.24 rows=44388 width=8) (actual time=0.021..79.879
> rows=43792 loops=1)
> Index Cond: (idword = 44)
> Total runtime: 107.081 ms
> (3 rows)
>
> Could someone provide an explanation for the planner's behaviour?
Pretty simple, really. Look at the cost calculations for the index scan for
the multi-column index. PostgreSQL believes that:
The cost of a seq scan is 4788.14
The cost of an 2-column index scan is 36720.39
The cost of a 1-column index scan is 916.24
Assuming that you ran each of these queries multiple times to eliminate
caching as a factor, the issue is that the cost calculations are wrong. We
give you a number of GUC variables to change that:
effective_cache_size
random_page_cost
cpu_tuple_cost
etc.
See the RUNTIME-CONFIGURATION docs for more details.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, d.crisan@ibcp.fr
Subject: Re: multi-column index
In-Reply-To: <200503161009.24713.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox