Обсуждение: Indexing timestamps
Hi all,
i have some questions on indexing a timestamp column of a table.
i have the following structure:
create table test_table( time_stamp datetime, id int8);
create index test_index on test_table using btree(time_stamp);
select count(*) from tbl_traffic;count
--------116894
I have questions on the following explains:
explain select * from test_table where time_stamp = datetime('2002-01-01');
NOTICE: QUERY PLAN:
Index Scan using test_index on test_table (cost=0.00..1651.78 rows=584 width=16)
explain select * from test_table where time_stamp = datetime(now());
NOTICE: QUERY PLAN:
Seq Scan on test_table (cost=0.00..2733.64 rows=584 width=16)
Why is the index test_index not used with the now() function?
If it is possible, how should i create i index that is used with now()?
explain select * from test_table where date_trunc('month',time_stamp) = date_trunc('month',datetime('2002-01-01'));
NOTICE: QUERY PLAN:
Seq Scan on test_table (cost=0.00..2441.41 rows=584 width=16)
explain select * from test_table where date_trunc('month',time_stamp) = date_trunc('month',datetime(now()));
NOTICE: QUERY PLAN:
Seq Scan on test_table (cost=0.00..3318.12 rows=584 width=16)
I need to select all data from this table monthly by select data of the last month.
Can anyone explain me why the second Seq scan takes longer than the first one and
is there i whay to define a index that is used if i do such a select, or better
is there a better select statement that uses some indexes??
Thanks in advance, hope someone could answer my questions :)
Andre,
I'm not even going to try to deal with the seq_scan vs. index_scan
issues on now(). This has been brought up in the list archives. Test
your actual response times as well as the query plan; you may find that
you don't have a real problem.
Something I can help you with:
> explain select * from test_table where date_trunc('month',time_stamp)
> = date_trunc('month',datetime('2002-01-01'));
> NOTICE: QUERY PLAN:
>
> Seq Scan on test_table (cost=0.00..2441.41 rows=584 width=16)
Try doing a:
CREATE INDEX idx_test_month ON test_table(extract(month FROM
time_stamp));
Which should help.
-Josh
On Thu, 6 Jun 2002, Josh Berkus wrote:
> Andre,
>
> I'm not even going to try to deal with the seq_scan vs. index_scan
> issues on now(). This has been brought up in the list archives. Test
> your actual response times as well as the query plan; you may find that
> you don't have a real problem.
>
> Something I can help you with:
>
> > explain select * from test_table where date_trunc('month',time_stamp)
> > = date_trunc('month',datetime('2002-01-01'));
> > NOTICE: QUERY PLAN:
> >
> > Seq Scan on test_table (cost=0.00..2441.41 rows=584 width=16)
>
> Try doing a:
> CREATE INDEX idx_test_month ON test_table(extract(month FROM
> time_stamp));
>
> Which should help.
Unfortunately you can't do that in that sort of syntax. You'll
need to create a function that returns the month and is marked
as iscachable and use that function in the index creation and
query.
Stephan, > Unfortunately you can't do that in that sort of syntax. You'll > need to create a function that returns the month and is marked > as iscachable and use that function in the index creation and > query. Hmmm. I take it that NOW() is not ISCACHABLE, and that's Andre's indexing problem? What would be the pitfall of creating an alternate version of NOW() that ISCACHABLE? -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Thu, 6 Jun 2002, Josh Berkus wrote: > > Stephan, > > > Unfortunately you can't do that in that sort of syntax. You'll > > need to create a function that returns the month and is marked > > as iscachable and use that function in the index creation and > > query. > > Hmmm. I take it that NOW() is not ISCACHABLE, and that's Andre's indexing > problem? What would be the pitfall of creating an alternate version of > NOW() that ISCACHABLE? I believe it was not marked as iscachable. I think it's now marked as static in dev sources which I would guess would mean that it could consider an index scan. I think with care an iscachable function that just called now() would probably be safe for interactive queries.
Stephan Szabo wrote: > On Thu, 6 Jun 2002, Josh Berkus wrote: > > > > > Stephan, > > > > > Unfortunately you can't do that in that sort of syntax. You'll > > > need to create a function that returns the month and is marked > > > as iscachable and use that function in the index creation and > > > query. > > > > Hmmm. I take it that NOW() is not ISCACHABLE, and that's Andre's indexing > > problem? What would be the pitfall of creating an alternate version of > > NOW() that ISCACHABLE? > > I believe it was not marked as iscachable. I think it's now marked as > static in dev sources which I would guess would mean that it could > consider an index scan. I think with care an iscachable function that > just called now() would probably be safe for interactive queries. We have all those new cacheable settings now to indicate things that can change during the transaction, function call, or never. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Fri, 7 Jun 2002, Bruce Momjian wrote: > Stephan Szabo wrote: > > On Thu, 6 Jun 2002, Josh Berkus wrote: > > > > > > > > Stephan, > > > > > > > Unfortunately you can't do that in that sort of syntax. You'll > > > > need to create a function that returns the month and is marked > > > > as iscachable and use that function in the index creation and > > > > query. > > > > > > Hmmm. I take it that NOW() is not ISCACHABLE, and that's Andre's indexing > > > problem? What would be the pitfall of creating an alternate version of > > > NOW() that ISCACHABLE? > > > > I believe it was not marked as iscachable. I think it's now marked as > > static in dev sources which I would guess would mean that it could > > consider an index scan. I think with care an iscachable function that > > just called now() would probably be safe for interactive queries. > > We have all those new cacheable settings now to indicate things that can > change during the transaction, function call, or never. Yeah, it's marked static now. The comments implied that meant constant for a particular scan which I'd guess means index scans are safe but I didn't go looking through code or try examples to verify that.