> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, June 10, 2003 8:50 PM
> To: Edmund Dengler
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Performance of query (fwd)
>
>
> Edmund Dengler <edmundd@eSentire.com> writes:
> > select * from event
> > where timestamp > (select now() - '2 hours'::interval)
> > and exists (select 1 from hack_pull_sid where sid = event.sid)
>
> > (note: <hack_pull_sid> is a table of SIDs I am interested
> in so that I
> > avoid the issues with IN)
>
> I think you're creating more issues than you're avoiding.
> With the above query, the planner has little chance of
> guessing how many rows will be retrieved from "event" ... and
> indeed the EXPLAIN output shows that its guess is off by more
> than a factor of 1000:
>
> > -> Index Scan using timestamp_idx on event
> > (cost=0.00..558165.62 rows=237893 width=24) (actual time=0.18..3.05
> > rows=129 loops=1)
>
> With a misestimate of that magnitude at the core of the
> query, it's unsurprising that all the other plan choices are bad too.
>
> But actually I suspect the easiest point of attack is not the
> EXISTS subquery, but the timestamp comparison. Can you get
> your application to supply a simple literal constant to
> compare to the timestamp, viz '2003-06-10 21:44' rather than
> now()-'2 hours'? The former gives the planner something to
> compare to its statistics, the latter doesn't.
In a case like that, wouldn't it be worthwhile having the planner
actually evaluate the expression?
IOW, the planner sees:
{column} <rel_op> {expression}
And it knows that {column} has a key (any type of key if relop is
'equals' and anything but hashed for all other relational operators)
Then go ahead and evaluation expression and directly substitute the
answer, unless expression is an aggregate.
So {column} <rel_op> {expression} becomes: {column} <rel_op>
{CONSTANT}
> Oh ... you have done an ANALYZE on event reasonably recently, no?