Skip site navigation (1) Skip section navigation (2)

Re: Wrong index choosen?

From: Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org>
To: Gaetano Mendola <mendola(at)bigfoot(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Wrong index choosen?
Date: 2004-07-23 12:01:56
Message-ID: Pine.LNX.4.44.0407231354520.9559-100000@zigo.dhs.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Fri, 23 Jul 2004, Gaetano Mendola wrote:

> empdb=# explain analyze select * from v_past_connections where login_time > '2004-07-21';
>                                                                    QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------
>   Index Scan using idx_user_logs_login_time on user_logs  (cost=0.00..12.90 rows=481 width=28) (actual time=7.338..661.300 rows=22477 loops=1)
>     Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone)
>   Total runtime: 676.472 ms
> (3 rows)

In this plan it estimates to get 481 but it got 22477. So the estimation 
was very wrong. You can increase the statistics tarhet on the login_time 
and it will probably be better (after the next analyze).

> why then the planner choose to do an index scan using the filter that
> retrieve a bigger ammount of rows ? A bug ?

Because it has to decide on the plan before it knows exactly what the 
result will be. As seen above, the estimation was wrong and thus the plan 
was not as good as it could have been.

In this case you probably also want to create a combined index on both
columns:

CREATE INDEX foo ON user_log (id_user, login_time);

ps. This letter belonged to pgsql-performance and not pgsql-hackers.

-- 
/Dennis Björklund


In response to

Responses

pgsql-performance by date

Next:From: JeffDate: 2004-07-23 12:07:23
Subject: Re: Performance over a LAN
Previous:From: Rod TaylorDate: 2004-07-23 11:43:07
Subject: Re: Performance over a LAN

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-07-23 13:49:05
Subject: Re: make LockRelation use top transaction ID
Previous:From: Zeugswetter Andreas SB SDDate: 2004-07-23 11:12:41
Subject: Re: Fixing PKs and Uniques in tablespaces

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group