help on speeding up a one table query

Hi.

I'm trying speed up a simple query on one table.

A lot of data. Yet the right index should make it quick.

Any suggestions are greatly appreciated. Thank you in advance.

The Details follow ...

PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

# \d total
Table "total"
Attribute | Type | Modifier
-----------+-----------------------+----------
tcode | character varying(12) | not null
week | numeric(6,0) | not null
region | character varying(10) | not null
units | numeric(10,0) |
ytd | numeric(10,0) |
rtd | numeric(10,0) |
Indices: total_region_week_units_ind,
total_tcode_week_ind,
total_units_week_reg_ind,
total_week_region_ind, x
total_week_tcode_ind,
total_week_tcode_region_ind,
total_week_units_ind x

# select relname, relkind, relpages, reltuples
from pg_class where relname like 'total%';
relname | relkind | relpages | reltuples
-----------------------------+---------+----------+-----------
total | r | 568194 | 40868073
total_region_week_units_ind | i | 279539 | 41608901
total_tcode_week_ind | i | 273724 | 40868073
total_units_week_reg_ind | i | 274504 | 40868073
total_week_region_ind | i | 205846 | 40868073
total_week_tcode_ind | i | 255226 | 40868073
total_week_tcode_region_ind | i | 306076 | 40868073
total_week_units_ind | i | 224916 | 40868073
(8 rows)

# other statistics:
selectiveness rows
----------------- ----------
number of tuples: 40,868,073
WHERE week=200218 363,638
AND region='TOTAL' 53,691

------------------------------------------------------------------
# 1.sql The query I want:

SELECT *
FROM total
WHERE week=200218
AND region='TOTAL'
ORDER BY units DESC
LIMIT 100
;

Elapse time: 0:06.09 (almost fast enough)

QUERY PLAN:
Limit (cost=1660.89..1660.89 rows=100 width=72)
-> Sort (cost=1660.89..1660.89 rows=409 width=72)
-> Index Scan using total_week_region_ind on total
(cost=0.00..1643.16 rows=409 width=72)

-------------------------------------------------------------------
# 2.sql Variation on a theme:
- Widen the selectiveness (remove region='TOTAL'), and
- Add an irrelavent column to the ORDER BY clause.
Too bad this is not what I need.

SELECT *
FROM total
WHERE week=200218
--AND region='TOTAL'
ORDER BY week DESC, units DESC
LIMIT 100
;

Elapse time: 0:01.19

QUERY PLAN:
Limit (cost=0.00..387.01 rows=100 width=72)
-> Index Scan Backward using total_week_units_ind on total
(cost=0.00..168082.02 rows=43430 width=72)

-------------------------------------------------------------------
# 3.sql Forcing to use another index:

SELECT *
FROM total
ORDER BY units DESC, week DESC, region DESC
LIMIT 100
;

Elapse Time: 0:00.07

QUERY PLAN:
Limit (cost=0.00..25.61 rows=100 width=72)
-> Index Scan Backward using total_units_week_reg_ind on total
(cost=0.00..10464433.90 rows=40868073 width=72)

-------------------------------------------------------------------
# 4.sql. Adding conditions to it:

SELECT *
FROM total
WHERE week=200218
AND region='TOTAL'
ORDER BY units DESC, week DESC, region DESC
LIMIT 100
;

Elapse Time: 0:11.88

QUERY PLAN:
Limit (cost=1660.89..1660.89 rows=100 width=72)
-> Sort (cost=1660.89..1660.89 rows=409 width=72)
-> Index Scan using total_week_region_ind on total
(cost=0.00..1643.16 rows=409 width=72)

Final comments:

I would expect the indexes :

total_region_week_units_ind, or

total_units_week_reg_ind

to be used to return tuple set in a fractional second. Which is what I
need (Web response time).

David Link
White Plains, NY