help on speeding up a one table query - Mailing list pgsql-general

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