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

On Thu, 20 Jun 2002, David Link wrote:

> 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)

Is it possible that an index on week,region,units and
 order by week desc, region desc, units desc will work
 better for you? Ordering in multicolumn indexes counts
 and I'd guess it'd want week and region first since
 that's the selection criteria.