Re: Timestamp index not used in some cases

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tuesday 12 May 2009 12:55:14 Scott Marlowe wrote:
> On Tue, May 12, 2009 at 3:00 AM, Евгений Василев
>
> <evasilev@xxxxxxxxxxxxxxxx> 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.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux