update@thyella:~$ time echo "select count(*) from plprice_00 where plno > 170813001;" | sqlplus pl/pl@local

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Aug 25 16:49:15 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL>
  COUNT(*)
----------
      9000

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

real    0m4.877s
user    0m0.030s
sys     0m0.020s
update@thyella:~$ time echo "select count(*) from plprice_00 where plno > 170813001;" | psql -U pl -d pl
 count
-------
  9000
(1 row)


real    0m4.991s
user    0m0.000s
sys     0m0.000s

pl=# create table test2 ( id smallint );
CREATE TABLE

$ cat test.pl
#!/usr/bin/perl
use strict;
use warnings;
my @data = (
  1   =>  2762907 ,
  4   =>  184 ,
  6   =>  28784676 ,
  17  =>  3144128 ,
  47  =>  2692 ,
  50  =>  4645 ,
  51  =>  1375 ,
  94  =>  296 ,
);
while ( (my $id = shift @data) && (my $num = shift @data) ) {
 for (my $i= 0 ; $i<$num; $i++) {
  printf "%d\n", $id;
 }
}


$ time ./test.pl | psql -U pl -d pl -c "copy test2 from stdin"

real    2m43.970s
user    1m26.890s
sys     0m1.030s

pl=# create index i2 on test2 ( id );
CREATE INDEX

pl=# VACUUM ANALYZE test2;
VACUUM

pl=# explain select count(*) from test2 where id> 17 ::smallint;
                              QUERY PLAN
 ---------------------------------------------------------------------- 
 Aggregate  (cost= 2 . 02 .. 2 . 02  rows= 1  width= 0 )
   ->  Index Scan using i2 on test2  (cost= 0 . 00 .. 2 . 01  rows= 1  width= 0 )
         Index Cond: (id >  17 ::smallint)
( 3  rows)

pl=# explain analyze select count(*) from test2 where id> 17 ::smallint;
                                                     QUERY PLAN
 --------------------------------------------------------------------------------------------------------------------- 
 Aggregate  (cost= 2 . 02 .. 2 . 02  rows= 1  width= 0 ) (actual time= 347 . 51 .. 347 . 51  rows= 1  loops= 1 )
   ->  Index Scan using i2 on test2  (cost= 0 . 00 .. 2 . 01  rows= 1  width= 0 ) (actual time= 307 . 59 .. 326 . 69  rows= 9008  loops= 1 )
         Index Cond: (id >  17 ::smallint)
 Total runtime:  361 . 96  msec
( 4  rows)

pl=# explain analyze select count(*) from test2 where id> 17 ::smallint;
                                                     QUERY PLAN
 --------------------------------------------------------------------------------------------------------------------- 
 Aggregate  (cost= 2 . 02 .. 2 . 02  rows= 1  width= 0 ) (actual time= 223 . 36 .. 223 . 36  rows= 1  loops= 1 )
   ->  Index Scan using i2 on test2  (cost= 0 . 00 .. 2 . 01  rows= 1  width= 0 ) (actual time= 197 . 67 .. 216 . 35  rows= 9008  loops= 1 )
         Index Cond: (id >  17 ::smallint)
 Total runtime:  223 . 41  msec

pl=# explain analyze select count(*) from test2 where id>= 18 ::smallint;
                                                    QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------ 
 Aggregate  (cost= 2 . 02 .. 2 . 02  rows= 1  width= 0 ) (actual time= 25 . 31 .. 25 . 31  rows= 1  loops= 1 )
   ->  Index Scan using i2 on test2  (cost= 0 . 00 .. 2 . 01  rows= 1  width= 0 ) (actual time= 0 . 03 .. 18 . 59  rows= 9008  loops= 1 )
         Index Cond: (id >=  18 ::smallint)
 Total runtime:  25 . 37  msec
( 4  rows)

pl=# create table test# ( id smallint );
$ time ./test#.pl | psql -c "copy test# from stdin"
pl=# create index i# on test# ( id );
pl=# ALTER TABLE test# ALTER COLUMN id SET STATISTICS  1000 ;
pl=# ANALYZE test#;

#!/usr/bin/perl
# test2.pl
use strict;
use warnings;
my @data = (
  1   =>  2762907 ,
  4   =>  184 ,
  6   =>  28784676 ,
  17  =>  3144128 ,
  47  =>  2692 ,
  50  =>  4645 ,
  51  =>  1375 ,
  94  =>  296 ,
);
while ( (my $id = shift @data) && (my $num = shift @data) ) {
 for (my $i= 0 ; $i<$num; $i++) {
  printf "%d\n", $id;
 }
}

#!/usr/bin/perl
# test3.pl
use strict;
use warnings;
my %data = (
  1  =>   15015 ,  #  2762907 
  4  =>   1 ,      #  184 
  6  =>   156438 , #  28784676 
  17  =>  17087 ,  #  3144128 
  47  =>  14 ,     #  2692 
  50  =>  25 ,     #  4645 
  51  =>  7 ,      #  1375 
  94  =>  2 ,      #  296 
);
my $min_cnt =  184 ;
for (my $j= 0 ; $j<$min_cnt; $j++) {
 for my $id ( sort { rand()<=> 0 . 5  } keys %data ) {
  for (my $i= 0 ; $i<$data{$id}; $i++) {
   printf "%d\n", $id;
  }
 }
}

#!/usr/bin/perl
# test4.pl
use strict;
use warnings;
my @data = (
 [ 0 . 829508  =>  6  ], #  28784676 ,
 [ 0 . 920114  =>  17 ], #  3144128 ,
 [ 0 . 999735  =>  1  ], #  2762907 ,
 [ 0 . 999869  =>  50 ], #  4645 ,
 [ 0 . 999947  =>  47 ], #  2692 ,
 [ 0 . 999986  =>  51 ], #  1375 ,
 [ 0 . 999995  =>  94 ], #  296 ,
 [ 1 . 000000  =>  4  ], #  184 ,
);
my $num = scalar @data;
my $cnt =  34700903 ;
OU: for (my $j= 0 ; $j<$cnt; $j++) {
 my $rnd = rand();
 IN: for (my $i= 0 ; $i<$num; $i++) {
  if ($data[$i]->[ 0 ]>$rnd) {
   printf "%d\n", $data[$i]->[ 1 ];
   last IN;
  }
 }
}

pl=# explain analyze select count(*) from test2 where id>= 47 ::smallint;
                                                        QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------- 
 Aggregate  (cost= 163 . 75 .. 163 . 75  rows= 1  width= 0 ) (actual time= 856 . 40 .. 856 . 40  rows= 1  loops= 1 )
   ->  Index Scan using i2 on test2  (cost= 0 . 00 .. 145 . 82  rows= 7172  width= 0 ) (actual time= 183 . 32 .. 849 . 58  rows= 9008  loops= 1 )
         Index Cond: (id >=  47 ::smallint)
 Total runtime:  895 . 07  msec
( 4  rows)

pl=# explain analyze select count(*) from test3 where id>= 47 ::smallint;
                                                         QUERY PLAN
 ---------------------------------------------------------------------------------------------------------------------------- 
 Aggregate  (cost= 9208 . 74 .. 9208 . 74  rows= 1  width= 0 ) (actual time= 9472 . 92 .. 9472 . 92  rows= 1  loops= 1 )
   ->  Index Scan using i3 on test3  (cost= 0 . 00 .. 9185 . 89  rows= 9138  width= 0 ) (actual time= 215 . 76 .. 9465 . 89  rows= 8832  loops= 1 )
         Index Cond: (id >=  47 ::smallint)
 Total runtime:  9472 . 98  msec
( 4  rows)

pl=# explain analyze select count(*) from test4 where id>= 47 ::smallint;
                                                          QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------------------ 
 Aggregate  (cost= 8962 . 73 .. 8962 . 73  rows= 1  width= 0 ) (actual time= 114142 . 94 .. 114142 . 94  rows= 1  loops= 1 )
   ->  Index Scan using i4 on test4  (cost= 0 . 00 .. 8940 . 46  rows= 8907  width= 0 ) (actual time= 202 . 91 .. 114125 . 40  rows= 8937  loops= 1 )
         Index Cond: (id >=  47 ::smallint)
 Total runtime:  114143 . 01  msec
( 4  rows)