From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Kalchev <daniel@digsys.bg>
Cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage
Date: Mon, 07 Jan 2002 14:41:07 -0500
Message-ID: <14149.1010432467@sss.pgh.pa.us> (raw)
In-Reply-To: <200201071841.UAA26328@dcave.digsys.bg>
References: <200201071841.UAA26328@dcave.digsys.bg>
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
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: tgl@sss.pgh.pa.us, daniel@digsys.bg
Subject: Re: again on index usage
In-Reply-To: <14149.1010432467@sss.pgh.pa.us>
* 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