| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Richard Huxton <dev(at)archonet(dot)com> |
| Cc: | Reiner Dassing <dassing(at)wettzell(dot)ifag(dot)de>, pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Select on timestamp-day slower than timestamp alone |
| Date: | 2003-05-06 13:59:08 |
| Message-ID: | 24863.1052229548@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
Richard Huxton <dev(at)archonet(dot)com> writes:
> Well, the "why" is because the number of rows recommended is so big
> (rows=10253528) - I'm also puzzled why we get "timestamp without time zone".
Because that's what he specified the constant to be.
> Does an explicit cast to "with time zone" help?
Writing the constant as timestamp with time zone would fix it.
Casting after-the-fact would not.
The reason: although both "timestamp minus interval" and "timestamptz
minus interval" are constant-foldable, timestamp-to-timestamptz
conversion is not (because it depends on SET TIMEZONE). So the
planner has to fall back to a default selectivity estimate. With real
constants it is able to derive a better estimate.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Reiner Dassing | 2003-05-06 14:00:32 | Re: Select on timestamp-day slower than timestamp alone |
| Previous Message | Tom Lane | 2003-05-06 13:45:07 | Re: [PERFORM] Hypothetical suggestions for planner, indexing improvement |