Thread: Timestamp indexes
A while back I as told (by Tom Lane I *think*) that timestamp (previously
datetime) fields couldn't be indexed as such and that I should index them
using this method :
CREATE INDEX "applicants_resubmitted" on "applicants" using btree ( date
("resubmitted") "date_ops" );
Since almost all the queries that search that field search it casting the
field to date, I thought that would be OK.. It was for a while (in the 6.5.X
days) but it seems that 7.0.2 is treating this different. I can't get an
index scan on that field no matter what I do.
Any suggestions?
Thanks!
-Mitch
"Mitch Vincent" <mitch@venux.net> writes:
> A while back I as told (by Tom Lane I *think*) that timestamp (previously
> datetime) fields couldn't be indexed as such
That's certainly not true now, if it ever was...
regression=# create table applicants(resubmitted timestamp);
CREATE
regression=# create index applicants_i on applicants(resubmitted);
CREATE
regression=# explain select * from applicants where resubmitted = 'today';
NOTICE: QUERY PLAN:
Index Scan using applicants_i on applicants (cost=0.00..8.14 rows=10 width=8)
EXPLAIN
> and that I should index them
> using this method :
> CREATE INDEX "applicants_resubmitted" on "applicants" using btree ( date
> ("resubmitted") "date_ops" );
> Since almost all the queries that search that field search it casting the
> field to date, I thought that would be OK.. It was for a while (in the 6.5.X
> days) but it seems that 7.0.2 is treating this different. I can't get an
> index scan on that field no matter what I do.
Dunno, it works for me ...
regression=# CREATE INDEX "applicants_resubmitted" on "applicants"
regression-# (date("resubmitted") "date_ops" );
CREATE
regression=# explain select * from applicants where date(resubmitted) = 'today';
NOTICE: QUERY PLAN:
Index Scan using applicants_resubmitted on applicants (cost=0.00..8.16 rows=10
width=8)
EXPLAIN
regression=# explain select * from applicants where resubmitted::date = 'today';
NOTICE: QUERY PLAN:
Index Scan using applicants_resubmitted on applicants (cost=0.00..8.16 rows=10
width=8)
EXPLAIN
You would want an index on date() of the field if this is what most of
your queries look like --- a straight index on the timestamp isn't
useful for such a query. But I don't know why you're not getting
index scans. More details please?
regards, tom lane
select * from applicants as a where (a.created::date > '05-01-2000' or a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 There is one of the queries.. I just remembered that the order by was added since last time I checked it's PLAN (in the 6.5.X days) -- could that be the problem? 8784 records in the applicant database. created and resubmitted are both timestamps. NOTICE: QUERY PLAN: Sort (cost=2011.65..2011.65 rows=4880 width=611) -> Seq Scan on applicants a (cost=0.00..1712.68 rows=4880 width=611) ProcessQuery ! system usage stats: ! 7.489270 elapsed 5.609119 user 1.730936 system sec ! [5.618921 user 1.750540 sys total] ! 1/546 [1/546] filesystem blocks in/out ! 0/9287 [0/9496] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/3 [3/6] messages rcvd/sent ! 7/102 [10/105] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand Thanks Tom! -Mitch
"Mitch Vincent" <mitch@venux.net> writes:
> select * from applicants as a where (a.created::date > '05-01-2000' or
> a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted >
> a.created then a.resubmitted else a.created end) desc limit 10 offset 0
> There is one of the queries.. I just remembered that the order by was added
> since last time I checked it's PLAN (in the 6.5.X days) -- could that be the
> problem?
Probably. With the ORDER BY in there, the LIMIT no longer applies
directly to the scan (since a separate sort step is going to be
necessary). Now it's looking at a lot more data to be fetched by
the scan, not just 10 records, so the indexscan becomes less attractive.
Might be interesting to compare the estimated and actual runtimes
between this query and what you get with "set enable_seqscan to off;"
regards, tom lane
With enable_seqscan off (Same query) Sort (cost=9282.89..9282.89 rows=4880 width=611) -> Index Scan using applicants_created, applicants_resubmitted on applicants a (cost=0.00..8983.92 rows=4880 width=611) ...and.. ! system usage stats: ! 7.541906 elapsed 5.368217 user 2.062897 system sec ! [5.391668 user 2.070713 sys total] ! 1/543 [2/543] filesystem blocks in/out ! 0/9372 [0/9585] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/3 [4/7] messages rcvd/sent ! 7/101 [12/107] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand Looks like that index scan is very unattractive... I'll look for some other ways to speed up the query a bit.. Thanks! -Mitch ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Mitch Vincent" <mitch@venux.net> Cc: <pgsql-sql@postgresql.org> Sent: Friday, July 21, 2000 1:26 PM Subject: Re: [SQL] Timestamp indexes > "Mitch Vincent" <mitch@venux.net> writes: > > select * from applicants as a where (a.created::date > '05-01-2000' or > > a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > > > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 > > > There is one of the queries.. I just remembered that the order by was added > > since last time I checked it's PLAN (in the 6.5.X days) -- could that be the > > problem? > > Probably. With the ORDER BY in there, the LIMIT no longer applies > directly to the scan (since a separate sort step is going to be > necessary). Now it's looking at a lot more data to be fetched by > the scan, not just 10 records, so the indexscan becomes less attractive. > > Might be interesting to compare the estimated and actual runtimes > between this query and what you get with "set enable_seqscan to off;" > > regards, tom lane >
"Mitch Vincent" <mitch@venux.net> writes:
> Looks like that index scan is very unattractive...
Yes, though not as bad as the cost estimator thinks (almost a 5:1 ratio
in estimated cost, but hardly any difference in real runtime). Still
have some work to do in tweaking the estimates, obviously.
regards, tom lane