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]