Thread: indices usage
Hello,
I have the following setup:
isp_leased=# \d traffic_stat
Table "traffic_stat"
Attribute | Type | Modifier
---------------+--------------------------+---------------
timestamp | timestamp with time zone | default now()
customer_name | character varying(20) |
class | smallint | default 0
bytes | integer |
code | smallint | default 0
source_ip | inet |
dest_ip | inet |
Indices: traffic_stat_idx1,
traffic_stat_idx2
isp_leased=# \d traffic_stat_idx1
Index "traffic_stat_idx1"
Attribute | Type
---------------+--------------------------
customer_name | character varying(20)
timestamp | timestamp with time zone
class | smallint
btree
isp_leased=# \d traffic_stat_idx2
Index "traffic_stat_idx2"
Attribute | Type
-----------+--------------------------
timestamp | timestamp with time zone
btree
There are two problems with indices.
First, traffic_stat_idx2 is not used even after I set enable_seqscan to false:
isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19');
NOTICE: QUERY PLAN:
Aggregate (cost=100012511.52..100012511.52 rows=1 width=0)
-> Seq Scan on traffic_stat (cost=100000000.00..100012399.96 rows=44622 width=0)
EXPLAIN
The question is why it doesn't make use of traffic_stat_idx2?
Second, traffic_stat_idx1 is used only when enable_seqscan is set to false:
isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19')and customer_name='tu';
NOTICE: QUERY PLAN:
Aggregate (cost=25525.42..25525.42 rows=1 width=0)
-> Index Scan using traffic_stat_idx1 on traffic_stat (cost=0.00..25520.88 rows=1815 width=0)
EXPLAIN
isp_leased=# set enable_seqscan=1;
SET VARIABLE
isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19')and customer_name='tu';
NOTICE: QUERY PLAN:
Aggregate (cost=13408.49..13408.49 rows=1 width=0)
-> Seq Scan on traffic_stat (cost=0.00..13403.95 rows=1815 width=0)
EXPLAIN
isp_leased=# select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19')and customer_name='tu';
count
-------
5791
(1 row)
isp_leased=# select count(*) from traffic_stat;
count
--------
402952
(1 row)
I think cost estimation is wrong in this case. "vacuum analyze" was performed
shortly before the test. Postgresql version is 7.1.3.
--
Dmitry O Panov | mailto:dmitry@tsu.tula.ru
Tula State University | http://www.tsu.tula.ru/
Dept. of CS & NIT | Fidonet: Dmitry Panov, 2:5022/5.50
Dmitry Panov <dmitry@tsu.tula.ru> writes:
> isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19');
> The question is why it doesn't make use of traffic_stat_idx2?
Try '2001-12-19'::timestamp, instead, or use CAST if you want to conform
to the standard. The function syntax is not considered a constant for
reasons I won't delve into here.
> I think cost estimation is wrong in this case.
Could easy be. Do you want to try it with 7.2 beta?
regards, tom lane
On Thu, Dec 20, 2001 at 10:27:06AM -0500, Tom Lane wrote:
> Dmitry Panov <dmitry@tsu.tula.ru> writes:
> > isp_leased=# explain select count(*) from traffic_stat where timestamp between timestamp('2001-12-18') and
timestamp('2001-12-19');
>
> > The question is why it doesn't make use of traffic_stat_idx2?
>
> Try '2001-12-19'::timestamp, instead, or use CAST if you want to conform
> to the standard. The function syntax is not considered a constant for
> reasons I won't delve into here.
Thanks, this solved both problems.
--
Dmitry O Panov | mailto:dmitry@tsu.tula.ru
Tula State University | http://www.tsu.tula.ru/
Dept. of CS & NIT | Fidonet: Dmitry Panov, 2:5022/5.50