pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (10+ messages)  latest in thread

Message-ID: <200503161009.24713.josh@agliodbs.com>
Permalink:  ../200503161009.24713.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200503161009.24713.josh@agliodbs.com

 · 

reply

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