Received: from dcave.digsys.bg (dcave.digsys.bg [192.92.129.5])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id g07IfCB46648
	for <pgsql-hackers@postgresql.org>; Mon, 7 Jan 2002 13:41:13 -0500 (EST)
	(envelope-from daniel@dcave.digsys.bg)
Received: from dcave.digsys.bg (daniel@localhost.digsys.bg [127.0.0.1])
	by dcave.digsys.bg (8.9.3/8.9.3) with ESMTP id UAA26328
	for <pgsql-hackers@postgresql.org>; Mon, 7 Jan 2002 20:41:08 +0200 (EET)
Message-Id: <200201071841.UAA26328@dcave.digsys.bg>
X-Mailer: exmh version 2.1.0 09/18/1999
From: Daniel Kalchev <daniel@digsys.bg>
To: pgsql-hackers@postgresql.org
Subject: again on index usage
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Mon, 07 Jan 2002 20:41:07 +0200
X-Archive-Number: 200201/282
X-Sequence-Number: 17335

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