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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to