pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Daniel Kalchev <daniel@digsys.bg>
To: pgsql-hackers@postgresql.org
Subject: again on index usage
Date: Mon, 07 Jan 2002 20:41:07 +0200
Message-ID: <200201071841.UAA26328@dcave.digsys.bg> (raw)

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




view thread (48+ messages)  latest in thread

Message-ID: <200201071841.UAA26328@dcave.digsys.bg>
Permalink:  ../200201071841.UAA26328@dcave.digsys.bg/
Also on:    postgresql.org/message-id/200201071841.UAA26328@dcave.digsys.bg

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: daniel@digsys.bg
  Subject: Re: again on index usage
  In-Reply-To: <200201071841.UAA26328@dcave.digsys.bg>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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