Re: Bad row estimates

From: Tom Lane
Subject: Re: Bad row estimates
Date: ,
Msg-id: 14850.1141488599@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Bad row estimates  (Greg Stark)
Responses: Re: Bad row estimates  (Greg Stark)
List: pgsql-performance

Tree view

Bad row estimates  (Alex Adriaanse, )
 Re: Bad row estimates  (Greg Stark, )
  Re: Bad row estimates  (Greg Stark, )
  Re: Bad row estimates  ("Jim C. Nasby", )
  Re: Bad row estimates  (Tom Lane, )
   Re: Bad row estimates  (Greg Stark, )
    Re: Bad row estimates  (Alex Adriaanse, )
     Re: Bad row estimates  (Greg Stark, )

Greg Stark <> writes:
> (I don't think the end_ts in the index is buying you much, despite its
> appearance in the Index Cond in the plan.)

Well, it saves some trips to the heap, but the indexscan is still going
to run from the beginning of the index to start_ts = now(), because
btree has no idea that there's any correlation between the two index
columns.

If you could put some a-priori bound on the interval width, you could
add a WHERE constraint "AND now() - max_width <= start_ts", which'd
constrain the index scan and possibly also get you a better planner
estimate.  Otherwise I think you really need a special datatype for time
intervals and a GIST or r-tree index on it :-(.

            regards, tom lane


pgsql-performance by date:

From: Kevin Brown
Date:
Subject: Re: How to query and index for customer with lastname and city
From: PFC
Date:
Subject: Planner enhancement suggestion.