pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Optimization with dates
11+ messages / 6 participants
[nested] [flat]

* Optimization with dates
@ 2001-11-13 23:56  Jean-Christophe Boggio <cat@thefreecat.org>
  0 siblings, 5 replies; 11+ messages in thread

From: Jean-Christophe Boggio @ 2001-11-13 23:56 UTC (permalink / raw)
  To: pgsql-sql

Hello,

I really have a problem dealing with dates :

I have a big table (~10M rows) like :

create table gains (
       dategain    timestamp,
       otherfields
);

There's an index on dategain and I want to use it to get the last
30 days records :

explain select count(*) from gains where dategain>=now()-30;

Aggregate  (cost=256546.78..256546.78 rows=1 width=0)
  ->  Seq Scan on gains  (cost=0.00..250627.68 rows=2367640 width=0)

whereas :
  
explain select count(*) from gains where dategain>='now';

Aggregate  (cost=27338.47..27338.47 rows=1 width=0)
  ->  Index Scan using ix_gains_dategain on gains  (cost=0.00..27320.71 rows=7103 width=0)

I have tried :
  where dategain>=(select now()-30);
  where dategain+30>='now'
  where date(dategain)>=date('now')-30
  and many other, syntactically absurd :-)

Anyone can help me use this index ?

TIA,
  
--
Jean-Christophe Boggio                       
cat@thefreecat.org                           -o)
Independant Consultant and Developer         /\\
Delphi, Linux, Perl, PostgreSQL, Debian     _\_V




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 00:17  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Jean-Christophe Boggio <cat@thefreecat.org>
  4 siblings, 0 replies; 11+ messages in thread

From: Stephan Szabo @ 2001-11-14 00:17 UTC (permalink / raw)
  To: Jean-Christophe Boggio <cat@thefreecat.org>; +Cc: pgsql-sql

On Wed, 14 Nov 2001, Jean-Christophe Boggio wrote:

> Hello,
>
> I really have a problem dealing with dates :
>
> I have a big table (~10M rows) like :
>
> create table gains (
>        dategain    timestamp,
>        otherfields
> );
>
> There's an index on dategain and I want to use it to get the last
> 30 days records :
>
> explain select count(*) from gains where dategain>=now()-30;
>
> Aggregate  (cost=256546.78..256546.78 rows=1 width=0)
>   ->  Seq Scan on gains  (cost=0.00..250627.68 rows=2367640 width=0)
>
> whereas :
>
> explain select count(*) from gains where dategain>='now';
>
> Aggregate  (cost=27338.47..27338.47 rows=1 width=0)
>   ->  Index Scan using ix_gains_dategain on gains  (cost=0.00..27320.71 rows=7103 width=0)
>
> I have tried :
>   where dategain>=(select now()-30);
>   where dategain+30>='now'
>   where date(dategain)>=date('now')-30
>   and many other, syntactically absurd :-)
>
> Anyone can help me use this index ?

Is 2367640 a reasonable estimate for the number of
rows that match the condition?  Have you run vacuum
analyze?
If the estimate is right, you'll probably find that
the sequence scan is actually faster than an index
scan since about 1/4 of the table is being selected.





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 00:19  Josh Berkus <josh@agliodbs.com>
  parent: Jean-Christophe Boggio <cat@thefreecat.org>
  4 siblings, 1 reply; 11+ messages in thread

From: Josh Berkus @ 2001-11-14 00:19 UTC (permalink / raw)
  To: Jean-Christophe Boggio <cat@thefreecat.org>; pgsql-sql

Jean-Christophe,

> Aggregate  (cost=256546.78..256546.78 rows=1 width=0)
>   ->  Seq Scan on gains  (cost=0.00..250627.68 rows=2367640 width=0)
> 
> whereas :

Hmmm... if the number of rows is actually accurate (2M out of 10M in the
last 30 days) then a Seq Scan seems like a good plan to me.  If the
numbers aren't accurate, it's time to run a VACUUM ANALYZE.

Regardless, if you're actually querying for 2M recordsets, you'd better
throw some hardware at the problem, and learn the .conf parameters.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 00:44  Jason Earl <jason.earl@simplot.com>
  parent: Jean-Christophe Boggio <cat@thefreecat.org>
  4 siblings, 1 reply; 11+ messages in thread

From: Jason Earl @ 2001-11-14 00:44 UTC (permalink / raw)
  To: Jean-Christophe Boggio <cat@thefreecat.org>; +Cc: pgsql-sql


I have a similar table (~16M rows) with an indexed timestamp, and have
had similar problems.  I have found that even when I am using a
constant timestamp like in this query.

SELECT * FROM caseweights1 WHERE dt > '2001-11-01';

I start getting sequential scans with 7.1.3 long before they are
faster than index based queries.  I believe that there is a constant
that can be fiddled to modify this behavior, and it seems like I have
also read that this constant has been modified in the new 7.2 release.
However, for queries that you *know* will be faster using the index
you can always issue:

set enable_seqscan to off;

before running your query.  This will force PostgreSQL to use the
index even in queries like this one:

SELECT * FROM caseweights1 WHERE dt > (SELECT now() - '30 days'::interval);

Jason

Jean-Christophe Boggio <cat@thefreecat.org> writes:

> Hello,
> 
> I really have a problem dealing with dates :
> 
> I have a big table (~10M rows) like :
> 
> create table gains (
>        dategain    timestamp,
>        otherfields
> );
> 
> There's an index on dategain and I want to use it to get the last
> 30 days records :
> 
> explain select count(*) from gains where dategain>=now()-30;
> 
> Aggregate  (cost=256546.78..256546.78 rows=1 width=0)
>   ->  Seq Scan on gains  (cost=0.00..250627.68 rows=2367640 width=0)
> 
> whereas :
>   
> explain select count(*) from gains where dategain>='now';
> 
> Aggregate  (cost=27338.47..27338.47 rows=1 width=0)
>   ->  Index Scan using ix_gains_dategain on gains  (cost=0.00..27320.71 rows=7103 width=0)
> 
> I have tried :
>   where dategain>=(select now()-30);
>   where dategain+30>='now'
>   where date(dategain)>=date('now')-30
>   and many other, syntactically absurd :-)
> 
> Anyone can help me use this index ?
> 
> TIA,
>   
> --
> Jean-Christophe Boggio                       
> cat@thefreecat.org                           -o)
> Independant Consultant and Developer         /\\
> Delphi, Linux, Perl, PostgreSQL, Debian     _\_V
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 00:49  Jason Earl <jason.earl@simplot.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Jason Earl @ 2001-11-14 00:49 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Jean-Christophe Boggio <cat@thefreecat.org>; pgsql-sql


Yikes!  Good catch.  My example database returns on ~.6M for the last
30 days, and an index scan still turns out to be a win (of course, it
turned out to be a bigger win to have a separate table with 15 minute
summarizations of the data :).

Josh is right, chances are good that a sequential scan will actually
perform better for you than an index scan if you are returning 20% of
your entire table.

Jason

"Josh Berkus" <josh@agliodbs.com> writes:

> Jean-Christophe,
> 
> > Aggregate  (cost=256546.78..256546.78 rows=1 width=0)
> >   ->  Seq Scan on gains  (cost=0.00..250627.68 rows=2367640 width=0)
> > 
> > whereas :
> 
> Hmmm... if the number of rows is actually accurate (2M out of 10M in the
> last 30 days) then a Seq Scan seems like a good plan to me.  If the
> numbers aren't accurate, it's time to run a VACUUM ANALYZE.
> 
> Regardless, if you're actually querying for 2M recordsets, you'd better
> throw some hardware at the problem, and learn the .conf parameters.
> 
> -Josh
> 
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 02:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jason Earl <jason.earl@simplot.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2001-11-14 02:20 UTC (permalink / raw)
  To: Jason Earl <jason.earl@simplot.com>; +Cc: Jean-Christophe Boggio <cat@thefreecat.org>; pgsql-sql

Jason Earl <jason.earl@simplot.com> writes:
> I have a similar table (~16M rows) with an indexed timestamp, and have
> had similar problems.  I have found that even when I am using a
> constant timestamp like in this query.

> SELECT * FROM caseweights1 WHERE dt > '2001-11-01';

> I start getting sequential scans with 7.1.3 long before they are
> faster than index based queries.

Just out of curiosity, do the indexed timestamps correlate closely to
the physical order of the table?  I'd expect that to happen if you
are timestamping records by insertion time and there are few or no
updates.

7.1 and before assume that the index order is random with respect to
the physical order, which is a good conservative assumption ... but it
results in drastically overestimating the cost of an indexscan when
strong correlation exists.  7.2 keeps some statistics about ordering
correlation, and may perhaps do better with this sort of situation.
(I have no doubt that its estimates will need further tweaking, but
at least the effect is somewhat accounted for now.)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 02:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jean-Christophe Boggio <cat@thefreecat.org>
  4 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2001-11-14 02:43 UTC (permalink / raw)
  To: Jean-Christophe Boggio <cat@thefreecat.org>; +Cc: pgsql-sql

Jean-Christophe Boggio <cat@thefreecat.org> writes:
> I have tried :
>   where dategain>=(select now()-30);
>   where dategain+30>='now'
>   where date(dategain)>=date('now')-30
>   and many other, syntactically absurd :-)

Try
	dategain >= ago(30)

where "ago" is a function that computes "date(now()) - n" and is
marked "iscachable".  This is a cheat but it keeps the planner from
being distracted by the noncachable nature of "now()".  You can find
past discussions of this if you search the archives for "iscachable",
eg
http://fts.postgresql.org/db/mw/msg.html?mid=1037521
http://fts.postgresql.org/db/mw/msg.html?mid=60584
http://fts.postgresql.org/db/mw/msg.html?mid=97823

As several other people pointed out, there's also a question of whether
the system *should* use the index --- you haven't told us how many
rows you expect the query to visit.  But until you express the WHERE
condition in the form "column >= something-that-can-be-reduced-to-
a-constant", you're unlikely to get the system to even try.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 12:02  Jean-Christophe Boggio <cat@thefreecat.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 11+ messages in thread

From: Jean-Christophe Boggio @ 2001-11-14 12:02 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Hello,

Thanks for all the answers. A little feedback :

>> I have tried :
>>   where dategain>=(select now()-30);
>>   and many other, syntactically absurd :-)

TL>         dategain >= ago(30)
TL> where "ago" is a function that computes "date(now()) - n" and is
TL> marked "iscachable".

create function ago(interval) returns timestamp as '
  select now() - $1
' language 'sql' with (iscachable);

explain select count(*) from gains where dategain>=ago('30 0:00');

Aggregate  (cost=180640.90..180640.90 rows=1 width=0)
  ->  Seq Scan on gains  (cost=0.00..179761.71 rows=351676 width=0)

=====
  
explain select count(*) from gains where dategain>=ago('5 days');

Aggregate  (cost=172340.65..172340.65 rows=1 width=0)
  ->  Index Scan using ix_gains_dategain on gains  (cost=0.00..172202.94 rows=55084 width=0)

=====
  
explain select count(*) from gains where dategain>=ago('6 days');

Aggregate  (cost=179929.06..179929.06 rows=1 width=0)
  ->  Seq Scan on gains  (cost=0.00..179761.71 rows=66940 width=0)  

TL> Just out of curiosity, do the indexed timestamps correlate closely to
TL> the physical order of the table?  I'd expect that to happen if you
TL> are timestamping records by insertion time and there are few or no
TL> updates.

That's right, there are very few updates.
  
===========================================
Now, for Jason's idea :

set enable_seqscan to off;
SET VARIABLE

explain select count(*) from gains where dategain>=now()-30;

Aggregate  (cost=100256770.86..100256770.86 rows=1 width=0)
  ->  Seq Scan on gains  (cost=100000000.00..100250847.08 rows=2369512 width=0)

Strange isn't it ?

Is it possible to do the equivalent of "set enable_seqscan to off"
out of psql (in php or perl code) ?

===========================================

To answer Stephan and Josh :

SS> Is 2367640 a reasonable estimate for the number of
SS> rows that match the condition?

JB> Hmmm... if the number of rows is actually accurate (2M out of 10M in the
JB> last 30 days) then a Seq Scan seems like a good plan to me.  If the
JB> numbers aren't accurate, it's time to run a VACUUM ANALYZE.

select avg(cnt) from (select count(*) as cnt from gains group by
 date(dategain)) as foo;
       avg
------------------
 12009.6131756757

If I did it right, this should be the average number of rows per day.
The db exists since April 1st 2000.

select date('now')-date('2000-04-01');
      592

select 592*12009;
  7109328

select count(*) from gains;
  count
---------
 7109753

As you see, dategain is *quite* linear !

So to answer your question, a reasonable estimate for the number of
rows that match the condition is :

select 30*12009;
   360270

The real answer is :

select count(*) from gains where dategain>=now()-30;
 231781
   
SS> Have you run vacuum analyze?

Every night (and it's a VEERRYYYY long process, even dropping the
indexes before and recreating them afterwards, maybe that's the real
problem ?) Keeping the index makes the VACUUM process several hours.
We'll try 7.2 which should solve part of this problem but since these
are production systems, we wait a little feedback from 7.2 users.

SS> If the estimate is right, you'll probably find that
SS> the sequence scan is actually faster than an index
SS> scan since about 1/4 of the table is being selected.

It should select 1/592 of the table !



Any further advises VERY appreciated. Thanks again everyone for your
help.

--
Jean-Christophe Boggio                       
cat@thefreecat.org                           -o)
Independant Consultant and Developer         /\\
Delphi, Linux, Perl, PostgreSQL, Debian     _\_V




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-14 17:17  Jason Earl <jason.earl@simplot.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Jason Earl @ 2001-11-14 17:17 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jason Earl <jason.earl@simplot.com>; Jean-Christophe Boggio <cat@thefreecat.org>; pgsql-sql

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Jason Earl <jason.earl@simplot.com> writes:
> > I have a similar table (~16M rows) with an indexed timestamp, and have
> > had similar problems.  I have found that even when I am using a
> > constant timestamp like in this query.
> 
> > SELECT * FROM caseweights1 WHERE dt > '2001-11-01';
> 
> > I start getting sequential scans with 7.1.3 long before they are
> > faster than index based queries.
> 
> Just out of curiosity, do the indexed timestamps correlate closely
> to the physical order of the table?  I'd expect that to happen if
> you are timestamping records by insertion time and there are few or
> no updates.

That's it precisely.  Records in this table are inserted only, and
never updated.  The records are timestamped when inserted.

> 7.1 and before assume that the index order is random with respect to
> the physical order, which is a good conservative assumption ... but
> it results in drastically overestimating the cost of an indexscan
> when strong correlation exists.  7.2 keeps some statistics about
> ordering correlation, and may perhaps do better with this sort of
> situation.  (I have no doubt that its estimates will need further
> tweaking, but at least the effect is somewhat accounted for now.)
> 
> 			regards, tom lane

I feel bad that I don't have 7.2b in testing yet.  I have been waiting
for a Debian package :).  I am quite excited about several new
features.  I read HACKERS pretty religiously and so I was aware that
you had added some new statistics and that you have fiddled a bit with
the constant that decides when a sequential scan is a good idea.  But
the fact of the matter is that I have already learned how to make
PostgreSQL do "the right thing" when it comes to using these indexes.
Besides, I generally only select from this table in 15 minute
intervals (~200 records) and PostgreSQL has no problems doing the
right thing by default.

The feature that I am most excited about is the new vacuum.  Currently
I can only vacuum this table (and several similar tables) once every
two weeks while the factory is down for maintenance.  This isn't a
very big deal as there are no updates or deletions from these tables
and the statistics in a ~16 million row table don't change much in two
weeks, but the new vacuum should do the correct thing for my tables
without any special handling on my part.

Thanks again,

Jason



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-19 07:32  Jean-Christophe Boggio <cat@thefreecat.org>
  parent: Jean-Christophe Boggio <cat@thefreecat.org>
  0 siblings, 0 replies; 11+ messages in thread

From: Jean-Christophe Boggio @ 2001-11-19 07:32 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql


So, no one can help ?

Should I stop defining indexes for date fields ?

ie: should DATE types be considered broken with no short time solution
and be replaced with strings ?

--
Jean-Christophe Boggio                       
cat@thefreecat.org                           -o)
Independant Consultant and Developer         /\\
Delphi, Linux, Perl, PostgreSQL, Debian     _\_V




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: Optimization with dates
@ 2001-11-21 00:28  Calvin Dodge <caldodge@fpcc.net>
  parent: Jean-Christophe Boggio <cat@thefreecat.org>
  4 siblings, 0 replies; 11+ messages in thread

From: caldodge@fpcc.net @ 2001-11-21 00:28 UTC (permalink / raw)
  To: pgsql-sql

cat@thefreecat.org (Jean-Christophe Boggio) wrote in message news:<72319017913.20011114005656@thefreecat.org>...
 
> I have tried :
>   where dategain>=(select now()-30);
>   where dategain+30>='now'
>   where date(dategain)>=date('now')-30
>   and many other, syntactically absurd :-)
>
> Anyone can help me use this index ?



^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2001-11-21 00:28 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-13 23:56 Optimization with dates Jean-Christophe Boggio <cat@thefreecat.org>
2001-11-14 00:17 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-14 00:19 ` Josh Berkus <josh@agliodbs.com>
2001-11-14 00:49   ` Jason Earl <jason.earl@simplot.com>
2001-11-14 00:44 ` Jason Earl <jason.earl@simplot.com>
2001-11-14 02:20   ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-14 17:17     ` Jason Earl <jason.earl@simplot.com>
2001-11-14 02:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-14 12:02   ` Jean-Christophe Boggio <cat@thefreecat.org>
2001-11-19 07:32     ` Jean-Christophe Boggio <cat@thefreecat.org>
2001-11-21 00:28 ` Calvin Dodge <caldodge@fpcc.net>

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