On Tuesday 12 May 2009 12:55:14 Scott Marlowe wrote:
> 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.
Thank you this worked like a charm.
