From: | Michael Fork <mfork(at)toledolink(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Index Scans |
Date: | 2000-06-05 19:10:56 |
Message-ID: | Pine.BSI.4.21.0006051501000.20428-100000@glass.toledolink.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Oops, ignore that first post :)
I am unable to get the following query to use an index scan, any
suggestions?
Query
-----
SELECT username, acctsessiontime, acctinputoctets, acctoutputoctets FROM
radacct WHERE ((tstamp >= '06-04-2000 00:00:00-04') AND (tstamp <
'06-05-2000 00:00:00-04') AND (acctstatustype = 'Stop')) ORDER BY username
ASC;
Indexes (I created this many for testing only)
-------
CREATE INDEX idx_radacct_1 ON radacct (username,acctstatustype,tstamp);
CREATE INDEX idx_radacct_2 ON radacct (username,acctstatustype);
CREATE INDEX idx_radacct_3 ON radacct (username,tstamp);
CREATE INDEX idx_radacct_4 ON radacct (acctstatustype,tstamp);
CREATE INDEX idx_radacct_5 ON radacct (tstamp);
CREATE INDEX idx_radacct_6 ON radacct (acctstatustype);
CREATE INDEX idx_radacct_7 ON radacct (username);
CREATE INDEX idx_radacct_8 ON radacct (tstamp,acctstatustype);
Here is the output of explain on the queries:
radius=# VACUUM ANALYZE;
VACUUM
radius=# set enable_seqscan=on;
SET VARIABLE
radius=# explain **INSERT QUERY**
NOTICE: QUERY PLAN:
Sort (cost=16528.92..16528.92 rows=5588 width=24)
-> Seq Scan on radacct (cost=0.00..16181.12 rows=5588 width=24)
EXPLAIN
radius=# set enable_seqscan=off;
SET VARIABLE
radius=# explain **INSERT QUERY**
NOTICE: QUERY PLAN:
Sort (cost=17500.82..17500.82 rows=5588 width=24)
-> Index Scan using idx_radacct_8 on radacct (cost=0.00..17153.01
rows=5588 width=24)
EXPLAIN
Thanks
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
From | Date | Subject | |
---|---|---|---|
Next Message | rocael | 2000-06-05 20:01:15 | moving DB |
Previous Message | Michael Fork | 2000-06-05 18:58:42 |