pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQueries with timestamps
4+ messages / 3 participants
[nested] [flat]
* Queries with timestamps
@ 2004-01-21 18:25 Arnau <arnaulist@andromeiberica.com>
0 siblings, 1 reply; 4+ messages in thread
From: Arnau @ 2004-01-21 18:25 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I'm quite newbie in SQL and I have a performance problem. I have the
following table (with some extra fields) and without any index:
CREATE TABLE STATISTICS
(
STATISTIC_ID NUMERIC(10) NOT NULL DEFAULT
nextval('STATISTIC_ID_SEQ')
CONSTRAINT pk_st_statistic_id
PRIMARY KEY,
TIMESTAMP_IN TIMESTAMP,
VALUE NUMERIC(10)
);
The queries on this table are mainly related with the timestamp field,
e.g.:
select * from statistics where time::date < current_date - interval
'1 month';
As the number of rows grows the time needed to execute this query takes
longer. What'd I should do improve the performance of this query?
Thank you very much
--
Arnau
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Queries with timestamps
@ 2004-01-21 19:06 Josh Berkus <josh@agliodbs.com>
parent: Arnau <arnaulist@andromeiberica.com>
0 siblings, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2004-01-21 19:06 UTC (permalink / raw)
To: Arnau <arnaulist@andromeiberica.com>; pgsql-performance
Arnau,
> As the number of rows grows the time needed to execute this query takes
> longer. What'd I should do improve the performance of this query?
Tip #1) add an index to the timestamp column
Tip #2) make sure that you VACUUM and ANALYZE regularly
Tip #3) You will get better performance if you pass the "current_date - 1
month" as a constant from the client instead of in the query. This is a
known issue, expected to be fixed in 7.5.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Queries with timestamps
@ 2004-01-21 20:38 Richard Huxton <dev@archonet.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2004-01-21 20:38 UTC (permalink / raw)
To: josh@agliodbs.com, "Arnau" <arnaulist@andromeiberica.com>; pgsql-performance
On Wednesday 21 January 2004 19:06, Josh Berkus wrote:
> Arnau,
>
> > As the number of rows grows the time needed to execute this query
> > takes longer. What'd I should do improve the performance of this query?
>
> Tip #1) add an index to the timestamp column
> Tip #2) make sure that you VACUUM and ANALYZE regularly
> Tip #3) You will get better performance if you pass the "current_date - 1
> month" as a constant from the client instead of in the query. This is a
> known issue, expected to be fixed in 7.5.
(I think Tip 3 is already fixed in 7.3, or I misunderstand what Josh is
saying)
Note that this is timestamp-related and not "timestamp with time zone"
related. Most of the time you want the latter anyway. If you can use with
time zones and drop the cast you might well find the index is being used...
EXPLAIN ANALYSE SELECT * FROM log_core WHERE log_ts > CURRENT_DATE - '1
week'::interval;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using log_core_ts_idx on log_core (cost=0.00..18.73 rows=239
width=117) (actual time=0.79..0.79 rows=0 loops=1)
Index Cond: (log_ts > ((('now'::text)::date - '7
days'::interval))::timestamp with time zone)
Total runtime: 1.03 msec
(3 rows)
It seems to help an accurate estimate of number-of-rows if you put an upper
and lower limit in.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Queries with timestamps
@ 2004-01-21 20:58 Josh Berkus <josh@agliodbs.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2004-01-21 20:58 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Arnau <arnaulist@andromeiberica.com>; pgsql-performance
Richard,
> (I think Tip 3 is already fixed in 7.3, or I misunderstand what Josh is
> saying)
Yeah? Certainly looks like it. Apparently I can't keep track.
I'd swear that this issue reared its ugly head again shortly before the 7.4
release.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-01-21 20:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-21 18:25 Queries with timestamps Arnau <arnaulist@andromeiberica.com>
2004-01-21 19:06 ` Josh Berkus <josh@agliodbs.com>
2004-01-21 20:38 ` Richard Huxton <dev@archonet.com>
2004-01-21 20:58 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox