Hello,

I have an table with ca 1.7 million records, with the following structure:

     Table "iplog_gate200112"
 Attribute |   Type    | Modifier 
-----------+-----------+----------
 ipaddr    | inet      | 
 input     | bigint    | 
 output    | bigint    | 
 router    | text      | 
 ipdate    | timestamp | 
Indices: iplog_gate200112_ipaddr_idx,
         iplog_gate200112_ipdate_idx

the following query 

explain
SELECT sum(input) FROM iplog_gate200112 
WHERE 
  '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' AND 
  '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02' AND 
   network_subeq(ipaddr, '193.68.240.0/20') AND 'uni-gw' ~ router;

results in 

NOTICE:  QUERY PLAN:

Aggregate  (cost=51845.51..51845.51 rows=1 width=8)
  ->  Seq Scan on iplog_gate200112  (cost=0.00..51845.04 rows=190 width=8)


Why would it not want to use index scan?

Statistics for the table are as follows (from pg_statistic s, pg_attribute a, 
pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'iplog_gate200112')

 attname | attdispersion | starelid  | staattnum | staop | stanullfrac | 
stacommonfrac |      stacommonval      |        staloval        |        
stahival
---------+---------------+-----------+-----------+-------+-------------+-------
--------+------------------------+------------------------+--------------------
----
 ipaddr  |   8.85397e-05 | 190565949 |         1 |  1203 |           0 |   
0.000441917 | 192.92.129.1           | 192.92.129.0           | 212.72.197.154
 input   |     0.0039343 | 190565949 |         2 |   412 |           0 |     
0.0183278 | 0                      | 0                      | 5929816798
 output  |      0.724808 | 190565949 |         3 |   412 |           0 |      
0.835018 | 0                      | 0                      | 2639435033
 router  |      0.222113 | 190565949 |         4 |   664 |           0 |      
0.416541 | sofia5                 | bourgas1               | varna3
 ipdate  |      0.014311 | 190565949 |         5 |  1322 |           0 |     
0.0580676 | 2001-12-04 00:00:00+02 | 2001-12-01 00:00:00+02 | 2001-12-31 
00:00:00+02
(5 rows)

The query 

explain
SELECT sum(input) FROM iplog_gate200112 
WHERE 
  ipdate < '2001-12-01 00:00:00+02'  AND 
  network_subeq(ipaddr, '193.68.240.0/20') AND 'uni-gw' ~ router;

produces 

Aggregate  (cost=4.91..4.91 rows=1 width=8)
  ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112  
(cost=0.00..4.91 rows=1 width=8)

Note there are no records with ipdate < '2001-12-01 00:00:00+02' in the table.

Could anyone sched some light? This is on 7.1.3.

Daniel


Mon, 07 Jan 2002 20:41:07 +0200
Daniel Kalchev <daniel@digsys.bg>
again on index usage
Daniel Kalchev <daniel@digsys.bg> writes:
> explain
> SELECT sum(input) FROM iplog_gate200112 
> WHERE 
>   '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' AND 
>   '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02' AND 
>    network_subeq(ipaddr, '193.68.240.0/20') AND 'uni-gw' ~ router;

> results in 

> NOTICE:  QUERY PLAN:

> Aggregate  (cost=51845.51..51845.51 rows=1 width=8)
>   ->  Seq Scan on iplog_gate200112  (cost=0.00..51845.04 rows=190 width=8)

> Why would it not want to use index scan?

It's difficult to tell from this what it thinks the selectivity of the
ipdate index would be, since the rows estimate includes the effect of
the ipaddr and router restrictions.  What do you get from just

explain
SELECT sum(input) FROM iplog_gate200112 
WHERE 
  '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' AND 
  '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02';

If you say "set enable_seqscan to off", does that change the plan?

BTW, the planner does not associate function calls with indexes.  If you
want to have the ipaddr index considered for this query, you need to write
ipaddr <<= '193.68.240.0/20' not network_subeq(ipaddr, '193.68.240.0/20').
(But IIRC, that only works in 7.2 anyway, not earlier releases :-()

			regards, tom lane

Mon, 07 Jan 2002 14:41:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: again on index usage 
> It's difficult to tell from this what it thinks the selectivity of the
> ipdate index would be, since the rows estimate includes the effect of
> the ipaddr and router restrictions.  What do you get from just
> 
> explain
> SELECT sum(input) FROM iplog_gate200112 
> WHERE 
>   '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' AND 
>   '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02';
> 

I don't know if this'd help, but if you're suming the data and running
this query often, see if a function index would help:

CREATE INDEX sum_input_fnc_idx ON iplog_gate200112 (sum(input));

-sc

-- 
Sean Chittenden

Mon, 7 Jan 2002 17:53:21 -0800
Sean Chittenden <sean@chittenden.org>
Re: again on index usage
>>>Tom Lane said:
 > It's difficult to tell from this what it thinks the selectivity of the
 > ipdate index would be, since the rows estimate includes the effect of
 > the ipaddr and router restrictions.  What do you get from just
 > 
 > explain
 > SELECT sum(input) FROM iplog_gate200112 
 > WHERE 
 >   '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' A
     ND 
 >   '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02';

Same result (sorry, should have included this originally):


Aggregate  (cost=47721.72..47721.72 rows=1 width=8)
  ->  Seq Scan on iplog_gate200112  (cost=0.00..47579.54 rows=56873 width=8)


 > If you say "set enable_seqscan to off", does that change the plan?

Yes. As expected (I no longer have the problem of NaN estimates :)

Aggregate  (cost=100359.71..100359.71 rows=1 width=8)
  ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112  
(cost=0.00..100217.52 rows=56873 width=8)

My belief is that the planner does not want to use index due to low value 
dispersion of the indexed attribute. When splitting the table into several 
smaller tables, index is used.

This bites me, because each such query takes at least 3 minutes and the script 
that generates these needs to execute few thousands queries.

 > BTW, the planner does not associate function calls with indexes.  If you
 > want to have the ipaddr index considered for this query, you need to write
 > ipaddr <<= '193.68.240.0/20' not network_subeq(ipaddr, '193.68.240.0/20').
 > (But IIRC, that only works in 7.2 anyway, not earlier releases :-()

This is what I though too, but using the ipdate index will be sufficient.

I understand my complaint is not a bug, but rather question of proper planner 
optimization (it worked 'as expected' in 7.0). Perhaps the planner should 
consider the total number of rows, as well as the dispersion factor. With the 
dispersion being around 1.5% and total rows 1.7 million this gives about 25k 
rows with the same value - large enough to trigger sequential scan, as far as 
I understand it, but the cost of scanning 1.7 million rows sequentially is 
just too high.

By the way, the same query takes approx 10 sec with set enable_seqscan to off.

Daniel


Tue, 08 Jan 2002 11:22:03 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
Daniel Kalchev <daniel@digsys.bg> writes:
> Same result (sorry, should have included this originally):

> Aggregate  (cost=47721.72..47721.72 rows=1 width=8)
>   ->  Seq Scan on iplog_gate200112  (cost=0.00..47579.54 rows=56873 width=8)

>>> If you say "set enable_seqscan to off", does that change the plan?

> Aggregate  (cost=100359.71..100359.71 rows=1 width=8)
>   ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112  
> (cost=0.00..100217.52 rows=56873 width=8)

So, what we've got here is a difference of opinion: the planner thinks
that the seqscan will be faster.  How many rows are actually selected
by this WHERE clause?  How long does each plan actually take?

			regards, tom lane

Tue, 08 Jan 2002 09:37:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: again on index usage 
>>>Tom Lane said:
 > Daniel Kalchev <daniel@digsys.bg> writes:
 > > Same result (sorry, should have included this originally):
 > 
 > > Aggregate  (cost=47721.72..47721.72 rows=1 width=8)
 > >   ->  Seq Scan on iplog_gate200112  (cost=0.00..47579.54 rows=56873 width=
     8)
 > 
 > >>> If you say "set enable_seqscan to off", does that change the plan?
 > 
 > > Aggregate  (cost=100359.71..100359.71 rows=1 width=8)
 > >   ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112  
 > > (cost=0.00..100217.52 rows=56873 width=8)
 > 
 > So, what we've got here is a difference of opinion: the planner thinks
 > that the seqscan will be faster.  How many rows are actually selected
 > by this WHERE clause?  How long does each plan actually take?
 > 
 > 			regards, tom lane

3-5 minutes with sequential scan; 10-15 sec with index scan. The query returns 
4062 rows. Out of ca 1700000 rows.

With only the datetime constraints (which relates to the index), the number of 
rows is 51764.

In any case, sequential scan of millions of rows cannot be faster than index 
scan. The average number of records for each index key is around 25000 - 
perhaps the planner thinks because the number of tuples in this case is 
higher, it should prefer sequential scan. I guess the planner will do better 
if there is some scaling of these values with respect to the total number of 
rows.

Daniel



Wed, 09 Jan 2002 09:45:51 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
Daniel Kalchev wrote:
> 
> >>>Tom Lane said:
>  > Daniel Kalchev <daniel@digsys.bg> writes:
>  > > Same result (sorry, should have included this originally):
>  >
>  > >>> If you say "set enable_seqscan to off", does that change the plan?
>  >
>  > > Aggregate  (cost=100359.71..100359.71 rows=1 width=8)
>  > >   ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112
>  > > (cost=0.00..100217.52 rows=56873 width=8)
>  >
>  > So, what we've got here is a difference of opinion: the planner thinks
>  > that the seqscan will be faster.  How many rows are actually selected
>  > by this WHERE clause?  How long does each plan actually take?
>  >
>  >                      regards, tom lane
> 
> 3-5 minutes with sequential scan; 10-15 sec with index scan. The query returns
> 4062 rows. Out of ca 1700000 rows.
> 
> With only the datetime constraints (which relates to the index), the number of
> rows is 51764.

The planner estimates 56873 rows. It seems not that bad.
A plausible reason is your table is nearly clustered 
as to ipdate.

regards,
Hiroshi Inoue

Wed, 09 Jan 2002 18:45:22 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: again on index usage
Daniel Kalchev <daniel@digsys.bg> writes:
> Aggregate  (cost=47721.72..47721.72 rows=1 width=8)
> ->  Seq Scan on iplog_gate200112  (cost=0.00..47579.54 rows=56873 width=
>      8)
>>> 
> If you say "set enable_seqscan to off", does that change the plan?
>>> 
> Aggregate  (cost=100359.71..100359.71 rows=1 width=8)
> ->  Index Scan using iplog_gate200112_ipdate_idx on iplog_gate200112  
> (cost=0.00..100217.52 rows=56873 width=8)
>>> 
>>> So, what we've got here is a difference of opinion: the planner thinks
>>> that the seqscan will be faster.  How many rows are actually selected
>>> by this WHERE clause?  How long does each plan actually take?

> 3-5 minutes with sequential scan; 10-15 sec with index scan. The query returns 
> 4062 rows. Out of ca 1700000 rows.

> With only the datetime constraints (which relates to the index), the number of 
> rows is 51764.

Hm.  Okay, so the number-of-rows estimate is not too far off.  I concur
with Hiroshi's comment: the reason the indexscan is so fast must be that
the table is clustered (physical order largely agrees with index order).
This would obviously hold if the records were entered in order by
ipdate; is that true?

The 7.2 planner does try to estimate index order correlation, and would
be likely to price this indexscan much lower, so that it would make the
right choice.  I'd suggest updating to 7.2 as soon as we have RC1 out.
(Don't do it yet, though, since we've got some timestamp bugs to fix
that are probably going to require another initdb.)

> In any case, sequential scan of millions of rows cannot be faster than index 
> scan.

Snort.  If that were true, we could get rid of most of the complexity
in the planner.

			regards, tom lane

Wed, 09 Jan 2002 10:14:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: again on index usage 
>>>Tom Lane said:
 > Hm.  Okay, so the number-of-rows estimate is not too far off.  I concur
 > with Hiroshi's comment: the reason the indexscan is so fast must be that
 > the table is clustered (physical order largely agrees with index order).
 > This would obviously hold if the records were entered in order by
 > ipdate; is that true?

Yes. But... do you want me to cluster it by ipaddr for example and try it 
again? I understand the clustering might help with sequential scans, but why 
would it help with index scans?

Daniel


Wed, 09 Jan 2002 18:37:49 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
Daniel Kalchev <daniel@digsys.bg> writes:
> I understand the clustering might help with sequential scans, but why 
> would it help with index scans?

No, the other way around: it makes no difference for seq scans, but can
speed up index scans quite a lot.  With a clustered table, successive
index-driven fetches tend to hit the same pages rather than hitting
random pages throughout the table.  That saves I/O.

Given the numbers you were quoting, if the table were in perfectly
random order by ipdate then there would probably have been about three
rows per page that the indexscan would've had to fetch.  This would mean
touching each page three times in some random order.  Unless the table
is small enough to fit in Postgres' shared buffer cache, that's going to
represent a lot of extra I/O --- a lot more than reading each page only
once, as a seqscan would do.  At the other extreme, if the table is
perfectly ordered by ipdate then the indexscan need only hit a small
number of pages (all the rows we want are in a narrow range) and we
touch each page many times before moving on to the next.  Very few I/O
requests in that case.

7.1 does not have any statistics about table order, so it uses the
conservative assumption that the ordering is random.  7.2 has more
statistical data and perhaps will make better estimates about the
cost of indexscans.

			regards, tom lane

Wed, 09 Jan 2002 14:48:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: again on index usage 
>>>Tom Lane said:
 > Daniel Kalchev <daniel@digsys.bg> writes:
 > > I understand the clustering might help with sequential scans, but why 
 > > would it help with index scans?
 > 
 > No, the other way around: it makes no difference for seq scans, but can
 > speed up index scans quite a lot.  With a clustered table, successive
 > index-driven fetches tend to hit the same pages rather than hitting
 > random pages throughout the table.  That saves I/O.

Ok, time to go home :-), but...

 > Given the numbers you were quoting, if the table were in perfectly
 > random order by ipdate then there would probably have been about three
 > rows per page that the indexscan would've had to fetch.  This would mean
 > touching each page three times in some random order.  Unless the table
 > is small enough to fit in Postgres' shared buffer cache, that's going to
 > represent a lot of extra I/O --- a lot more than reading each page only
 > once, as a seqscan would do.  At the other extreme, if the table is
 > perfectly ordered by ipdate then the indexscan need only hit a small
 > number of pages (all the rows we want are in a narrow range) and we
 > touch each page many times before moving on to the next.  Very few I/O
 > requests in that case.

In any case, if we need to hit 50k pages (assuming the indexed data is 
randomly scattered in the file), and having to read these three times each, it 
will be less I/O than having to read 1.7 million records. The table will never 
be laid sequentially on the disk, at least not in this case (which adds to the 
table day after day - and this is why data is almost ordered by ipdate).

What I am arguing about is the scaling - is 50k random reads worse than 1.7 
million sequential reads? Eventually considering the tuple size, disk block 
size etc.

I will wait patiently for 4.2 to release and see how this same table performs. 
:-)

Daniel


Wed, 09 Jan 2002 22:06:43 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
Daniel Kalchev <daniel@digsys.bg> writes:
> In any case, if we need to hit 50k pages (assuming the indexed data is 
> randomly scattered in the file), and having to read these three times each, it 
> will be less I/O than having to read 1.7 million records.

How do you arrive at that?  Assuming 100 records per page (probably the
right order of magnitude), the seqscan alternative is 17k page reads.
Yes, you examine more tuples, but CPUs are lots faster than disks.

That doesn't even address the fact that Unix systems reward sequential
reads and penalize random access.  In a seqscan, we can expect that the
kernel will schedule the next page read before we ask for it, so that
our CPU time to examine a page is overlapped with I/O for the next page.
In an indexscan that advantage goes away, because neither we nor the
kernel know which page will be touched next.  On top of the loss of
read-ahead, the filesystem is probably laid out in a way that rewards
sequential access with fewer and shorter seeks.

The tests I've made suggest that the penalty involved is about a factor
of four -- ie, a seqscan can scan four pages in the same amount of time
that it takes to bring in one randomly-accessed page.

			regards, tom lane

Wed, 09 Jan 2002 15:20:59 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: again on index usage 
>>>Tom Lane said:
 > Daniel Kalchev <daniel@digsys.bg> writes:
 > > In any case, if we need to hit 50k pages (assuming the indexed data is 
 > > randomly scattered in the file), and having to read these three times each
     , it 
 > > will be less I/O than having to read 1.7 million records.
 > 
 > How do you arrive at that?  Assuming 100 records per page (probably the
 > right order of magnitude), the seqscan alternative is 17k page reads.
 > Yes, you examine more tuples, but CPUs are lots faster than disks.

I tried this:

db=# select * into iplog_test from iplog_gate200112;
SELECT
db=# create index iplog_test_ipaddr_idx on iplog_test(ipaddr);
CREATE
db=# cluster iplog_test_ipaddr_idx on iplog_test;
CLUSTER
db=# create index iplog_test_ipdate_idx on iplog_test(ipdate);
CREATE
db=# vacuum verbose analyze iplog_test;
NOTICE:  --Relation iplog_test--
NOTICE:  Pages 17722: Changed 0, reaped 0, Empty 0, New 0; Tup 1706202: Vac 0, 
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 80, MaxLen 88; Re-using: Free/Avail. 
Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 1.48s/-1.86u sec.
NOTICE:  Index iplog_test_ipaddr_idx: Pages 5621; Tuples 1706202. CPU 
0.51s/1.80u sec.
NOTICE:  Index iplog_test_ipdate_idx: Pages 4681; Tuples 1706202. CPU 
0.36s/1.92u sec.
NOTICE:  --Relation pg_toast_253297758--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 
0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0; 
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_toast_253297758_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  Analyzing...
VACUUM
db=# explain
db-# SELECT sum(input), sum(output) FROM iplog_test 
db-# WHERE 
db-# '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' 
AND
db-# '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2002-01-01 00:00:00+02' 
AND
db-# ipaddr <<= '193.68.240.0/20' AND 'uni-gw' ~ router;
NOTICE:  QUERY PLAN:

Aggregate  (cost=56112.97..56112.97 rows=1 width=16)
  ->  Seq Scan on iplog_test  (cost=0.00..56111.54 rows=284 width=16)

EXPLAIN

query runs for ca 3.5 minutes.

db=# set enable_seqscan to off;

the query plan is

Aggregate  (cost=100507.36..100507.36 rows=1 width=16)
  ->  Index Scan using iplog_test_ipdate_idx on iplog_test  
(cost=0.00..100505.94 rows=284 width=16)

query runs for ca 2.2 minutes.

Moves closer to your point :-)

Anyway, the platform is an dual Pentium III 550 MHz (intel) machine with 512 
MB RAM, with 15000 RPM Cheetah for the database, running BSD/OS 4.2. The 
machine is reasonably loaded all the time, so this is very much real-time test.

I agree, that with the 'wrong' clustering the index scan is not so much faster 
than the sequential scan.

Perhaps I need to tune this machine's costs to prefer more disk intensive 
operations over CPU intensive operations?

Let's see what 4.2 will result in.

Daniel


Thu, 10 Jan 2002 11:39:31 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
Daniel Kalchev <daniel@digsys.bg> writes:
> I agree, that with the 'wrong' clustering the index scan is not so
> much faster than the sequential scan.

It would be interesting to check whether there is any correlation
between ipaddr and ipdate in your test data.  Perhaps clustering
on ipaddr might not destroy the ordering on ipdate as much as you
thought.  A more clearly random-order test would go:

select * into iplog_test from iplog_gate200112 order by random();
create index iplog_test_ipdate_idx on iplog_test(ipdate);
vacuum verbose analyze iplog_test;
<< run queries >>

> Perhaps I need to tune this machine's costs to prefer more disk intensive 
> operations over CPU intensive operations?

Possibly.  I'm not sure there's much point in tuning the cost estimates
until the underlying model is more nearly right (ie, knows something
about correlation).  Do you care to try your dataset with 7.2 beta?

			regards, tom lane

Thu, 10 Jan 2002 10:03:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: again on index usage 
>>>Tom Lane said:
 > It would be interesting to check whether there is any correlation
 > between ipaddr and ipdate in your test data.  Perhaps clustering
 > on ipaddr might not destroy the ordering on ipdate as much as you
 > thought.  A more clearly random-order test would go:
 > 
 > select * into iplog_test from iplog_gate200112 order by random();
 > create index iplog_test_ipdate_idx on iplog_test(ipdate);
 > vacuum verbose analyze iplog_test;
 > << run queries >>

NOTICE:  --Relation iplog_test--
NOTICE:  Pages 17761: Changed 17761, reaped 0, Empty 0, New 0; Tup 1706202: 
Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 80, MaxLen 88; Re-using: 
Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 2.36s/0.32u sec.
NOTICE:  Index iplog_test_ipdate_idx: Pages 4681; Tuples 1706202. CPU 
0.26s/1.98u sec.
NOTICE:  --Relation pg_toast_275335644--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 
0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0; 
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_toast_275335644_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  Analyzing...


explain (with enable_seqscan to on)

Aggregate  (cost=56151.97..56151.97 rows=1 width=16)
  ->  Seq Scan on iplog_test  (cost=0.00..56150.54 rows=284 width=16)

average run time  4 minutes

(iostat before seq run)

 tin tout   sps  tps msps   sps  tps msps  usr nic sys int idl
   5   44    94    6  5.9  6842  120  3.3   25  11   6   0  58
   1   14    27    1  5.9  5968  138  3.6   22  11   5   0  62
   5   44    58    2  6.3  5647  117  3.0   27   9   6   0  58
   1   13     7    1  5.0  5723  125  3.4   24  10   5   0  61
   0   15    50    2  4.5  5606  110  3.1   27  10   5   0  58
   5   44    90    5  9.1  4702   87  2.5   28  12   4   0  55
   1   15    52    1  6.3  5045  114  4.1   24  10   4   0  61

(iostat during seq run)

 tin tout   sps  tps msps   sps  tps msps  usr nic sys int idl
   1   40    41    2  2.1  5847  123  3.6   25  11   4   0  60
   1   16   164   13  6.2  7280  128  3.2   28   8   8   0  57
   0   13    36    1  7.9  6059  147  3.9   25   8   5   0  62
   0   13    48    3  5.3  6691  126  3.4   26   8   7   0  59
   0   13    28    3  4.6  6473  103  2.3   28  11   7   0  54
   0   13   138   11  7.6  10848  151  4.9   19   6   6   0  69
   0   13    33    3  3.3  5568  100  3.6   21  16   3   0  59
   0   13    24    2  1.1  6752  144  3.4   22  12   2   0  64

(sometime at the end of query run)

 tin tout   sps  tps msps   sps  tps msps  usr nic sys int idl
   0   38    20    2  5.5  5621   57  1.2   23  23   3   0  51
   0   13    89    7  7.7  8854  101  3.4   21  18   4   0  57
   0   13    72    6  7.3  9929   88  2.2   18  21   4   0  57
   0   13   129    6  9.6  4865   43  1.0   21  24   4   0  51
   0   13    72    3  4.2  5421   46  0.5   24  22   4   0  50
   0   13    52    2  3.5  5877   64  1.8   22  21   4   0  53
   0   13    50    3  6.1  5561   54  1.7   19  26   3   0  52
   0   13    42    1  3.8  5455   76  1.4   22  22   3   0  53

(see lower msps - perhaps other queries slowed down? - but then again returned 
to 'normal')

   0   13   244   20  6.6  6629  199  4.1   19   9   5   0  67
   0   13    68    4  6.2  6080  191  4.3   14  14   3   0  70
   0   13    75    3  5.9  6542  214  4.1   19   8   4   0  70
   0   13   615   18  5.0  5454  129  4.1   20  18   3   0  59
   0   13    88    2  5.7  3718   48  2.5   21  21   4   0  54
   0   13    46    3  3.1  4533   75  2.9   20  19   5   0  56
   0   13   143    7  5.1  4349   58  2.7   22  18   4   0  55
   0   13    58    2  9.9  4038   45  2.0   20  24   4   0  52
   0   13   111    4  5.8  4523   60  3.4   18  22   4   0  56

(with enable_seqscan to off)

Aggregate  (cost=85110.08..85110.08 rows=1 width=16)
  ->  Index Scan using iplog_test_ipdate_idx on iplog_test  
(cost=0.00..85108.66 rows=284 width=16)

average run time 2 minutes (worst 5 minutes, but this happened only once)

(iostat output)

 tin tout   sps  tps msps   sps  tps msps  usr nic sys int idl
   0   38     1    1  6.7  5110  224  3.2    7  16   3   0  73
   0   13    21    2  3.6  5249  219  4.1   12  13   2   0  73
   0   13     0    0 10.0  5341  216  3.9   12  11   4   0  73
   0   13     6    0  9.9  5049  218  3.5   10  14   3   0  72
   0   13     6    0  0.0  7654  216  3.8   10  10   2   0  78
   0   13     2    1  4.0  8758  222  4.1    6  11   4   0  80
   0   13     6    0  9.9  8594  219  4.4    6  10   3   0  81
   0   13     4    1  0.0  7290  210  4.3    6  10   4   0  80
   0   13    36    3  4.9  5912  196  4.7    9  10   4   0  76
   0   13     7    1 13.3  4787  209  3.7    9  17   2   0  72
   0   13     0    0 10.0  4691  209  3.8    9  17   2   0  72

This sort of proves that Tom is right about the need for random data. <grin>

Having the change to play with it at such idle time in the morning, when 
nobody is supposed to be working with the databae I dared to shut down all 
other sessions

(idle iostat)

iostat 5
      tty             sd0             sd1                % cpu
 tin tout   sps  tps msps   sps  tps msps  usr nic sys int idl
   0  559   148    2  7.7     8    1  3.3    0   0   0   0  99
   0  559   235    4  4.1    30    2  2.0    0   0   1   0  98
   0  559   189    2  8.7     3    0 10.0    0   0   1   0  99


seq scan (10 sec)

   0  575  12103  101  9.2  18798  164  1.4   19   0   8   0  73
   0  575  10118   82 11.2  30507  243  0.9   33   0  11   0  55
   0  559  12704  101  9.3  7642   61  0.7    9   0   8   0  83


index scan 45 sec

   0   38     8    1  4.0  6179  375  2.5    1   0   2   0  98
   0   13     4    1  2.5  6208  378  2.4    1   0   2   0  97
   0   13     7    1  3.3  6323  382  2.4    1   0   2   0  97
   0   13     9    2  0.0  6310  389  2.4    1   0   2   0  97
   0   13     0    0  0.0  3648  226  2.3    0   0   1   0  98

This proves to me 15000 RPM Cheetah drives are damn fast at sequential reads.
Why index scan wins at 'normal' database load??? I would expect that if the 
disk subsystem is overloaded, it will be more overloaded seek-wise, than 
troughput-wise. Perhaps this penalizes the 'expected to be faster' sequential 
reads much more than the random page index reads.

I will try 4.2 with the same data as soon as available and see the differences 
- on a separate machine with about the same configuration (same OS,same 
postgres install, same disks, only one 733 MHz CPU instead of two 550 MHz 
CPUs).

Daniel


Fri, 11 Jan 2002 07:32:25 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: again on index usage 
> -----Original Message-----
> From: Daniel Kalchev
> 
> I tried this:
> 
> db=# select * into iplog_test from iplog_gate200112;
> SELECT
> db=# create index iplog_test_ipaddr_idx on iplog_test(ipaddr);
> CREATE
> db=# cluster iplog_test_ipaddr_idx on iplog_test;
> CLUSTER
> db=# create index iplog_test_ipdate_idx on iplog_test(ipdate);
> CREATE
> db=# explain
> db-# SELECT sum(input), sum(output) FROM iplog_test 

> db-# WHERE 
> db-# '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 
> 00:00:00+02' 

Is there only one ipdate value which satisfies the above where clause ?

regards,
Hiroshi Inoue

Tue, 15 Jan 2002 23:10:55 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: again on index usage 
> -----Original Message-----
> From:  Hiroshi Inoue
> 
> > -----Original Message-----
> > From: Daniel Kalchev
> > 
> > I tried this:
> > 
> > db=# explain
> > db-# SELECT sum(input), sum(output) FROM iplog_test 
> 
> > db-# WHERE 
> > db-# '2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 
> > 00:00:00+02' 
> 
> Is there only one ipdate value which satisfies the above where clause ?

If '2001-12-01 00:00:00+02' is the unique ipdate value which satsifies
'2001-12-01 00:00:00+02' <= ipdate AND ipdate < '2001-12-02 00:00:00+02' 
and CREATE INDEX preserves the physical order of the same key,
the IndexScan would see physically ordered tuples. There's no strangeness
even if the scan is faster than sequential scan.

regards,
Hiroshi Inoue 

Wed, 16 Jan 2002 05:50:04 +0900
"Hiroshi Inoue" <Inoue@tpf.co.jp>
Re: again on index usage 