On Tue, May 12, 2009 at 3:00 AM, Евгений Василев
<evasilev@jarcomputers.com> wrote:
> I have the following table:
>
> CREATE TABLE "temp".tmp_135528
> (
> id integer NOT NULL,
> prid integer,
> group_id integer,
> iinv integer,
> oinv integer,
> isum numeric,
> osum numeric,
> idate timestamp without time zone,
> odate timestamp without time zone,
> CONSTRAINT t_135528_pk PRIMARY KEY (id)
> )
> WITH (OIDS=FALSE);
>
> With index:
>
> CREATE INDEX t_135528
> ON "temp".tmp_135528
> USING btree
> (idate, group_id, osum, oinv);
>
> When the following query is executed the index is not used:
>
> EXPLAIN SELECT id, osum
> FROM temp.tmp_135528
> WHERE idate <= '2007-05-17 00:00:00'::timestamp
> AND group_id = '13'
> AND osum <= '19654.45328'
> AND oinv = -1
>
> QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on tmp_135528 (cost=0.00..7022.36 rows=1166 width=11)
> Filter: ((idate <= '2007-05-17 00:00:00'::timestamp without time zone) AND
> (osum <= 19654.45328) AND (group_id = 13) AND (oinv = (-1)))
> (2 rows)
>
> When
> "idate <= '2007-05-17 00:00:00'::timestamp"
> is changed to
> "idate >= '2007-05-17 00:00:00'::timestamp"
> or
> "idate = '2007-05-17 00:00:00'::timestamp"
> then the index is used:
>
> EXPLAIN SELECT id, osum
> FROM temp.tmp_135528
> WHERE idate >= '2007-05-17 00:00:00'::timestamp
> AND group_id = '13'
> AND osum <= '19654.45328'
> AND oinv = -1;
> QUERY PLAN
>
-----------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using t_135528 on tmp_135528 (cost=0.00..462.61 rows=47 width=11)
> Index Cond: ((idate >= '2007-05-17 00:00:00'::timestamp without time zone)
> AND (group_id = 13) AND (osum <= 19654.45328) AND (oinv = (-1)))
> (2 rows)
>
> Why I cannot use the index in <= comparison on timestamp ?
You can. But in this instance one query is returning 47 rows while
the other is returning 1166 rows (or the planner thinks it is).
There's a switchover point where it's cheaper to seq scan. You can
adjust this point up and down by adjusting various costs parameters.
random_page_cost is commonly lowered to the 1.5 to 2.0 range, and
effective_cache_size is normally set higher, to match the cache in the
kernel plus the shared_buffer size.
