Thread: 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
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.
Stephan Szabo wrote: > > 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. Well I'll be. With all my thoroughness I missed this. It works. hugs and kisses to you. 5.sql Works! SELECT * FROM total WHERE week=200218 AND region='TOTAL' ORDER BY region DESC, week DESC, units DESC LIMIT 100 ; Elapse Time: 0:00.56 QUERY PLAN: Limit (cost=0.00..402.68 rows=100 width=72) -> Index Scan Backward using total_region_week_units_ind on total (cost=0.00..1676.03 rows=416 width=72) This uses the region/week/units index which is the good thing. Interestingly, The units/week/region index is nver used (I guess because the selectiveness takes a priority over order, as you hinted). Interestingly 2: Removing the 'Superficial' declaration of 'region DESC, week DESC' from the ORDER BY clause causes PG to no longer use the correct index and performance goes thru the roof. Thanks David