Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E2D814762EB for ; Fri, 17 Jan 2003 09:57:01 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 50161476199 for ; Fri, 17 Jan 2003 09:57:01 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0HEv45u011752; Fri, 17 Jan 2003 09:57:04 -0500 (EST) To: Timur Irmatov Cc: pgsql-performance@postgresql.org Subject: Re: index usage In-reply-to: <10597430257.20030117164800@sarkor.com> References: <10597430257.20030117164800@sarkor.com> Comments: In-reply-to Timur Irmatov message dated "Fri, 17 Jan 2003 16:48:00 +0500" Date: Fri, 17 Jan 2003 09:57:04 -0500 Message-ID: <11751.1042815424@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/178 X-Sequence-Number: 825 Timur Irmatov writes: > Limit (cost=0.00..0.19 rows=1 width=6) (actual time=0.43..0.43 rows=0 loops=1) > -> Index Scan using timeindex on mediumstats (cost=0.00..2898.96 rows=15185 width=6) (actual time=0.42..0.42 rows=0 loops=1) The planner has absolutely no clue about the behavior of your function, and so its estimate of the number of rows matched is way off, leading to a poor estimate of the cost of an indexscan. There is not much to be done about this in the current system (though I've speculated about the possibility of computing statistics for functional indexes). Just out of curiosity, why don't you lose all this year/month/day stuff and use a timestamp column? Less space, more functionality. regards, tom lane