Re: INDEX suggestion needed

Thomas,

Are you sure about ( visit >= '2002-12-01' OR visit <= '2002-12-11')?
Shouldn't it be AND?
If visit is sorted that mean all record as in if a > 10 or a < 20...
Or maybe you comparator are wrong.

JLL

Thomas Beutin wrote:
>
> On Thu, Dec 12, 2002 at 12:56:38AM -0500, Tom Lane wrote:
> > Thomas Beutin <tyrone(at)laokoon(dot)IN-Berlin(dot)DE> writes:
> > > i cannot create an index like this:
> > > CREATE INDEX "stat_pages_datum_idx" on "stat_pages" ( date ("visit") );
> >
> > You are overthinking the problem. A plain index on "visit" would work
> > fine for the example query you gave.
> Unfortunally it doesn't work, i'm still running in sequence
> scans all the time:
> tb=# CREATE INDEX tb5 ON stat_pages (visit);
> CREATE
> tb=# VACUUM FULL ANALYZE stat_pages;
> VACUUM
> tb=# EXPLAIN select count(distinct a_id) from stat_pages WHERE m_id = 35 AND ( visit >= '2002-12-01' OR visit <= '2002-12-11');
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=14679.99..14679.99 rows=1 width=34)
> -> Seq Scan on stat_pages (cost=0.00..13821.19 rows=343520 width=34)
>
> Is there any hope left? ;-)
>
> -tb
> --
> Thomas Beutin tb(at)laokoon(dot)IN-Berlin(dot)DE
> Beam me up, Scotty. There is no intelligent live down in Redmond.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)