[PERFORM] strategies for optimizing read on rather large tables
hi
first let me draw the outline.
we have a database which stores adverts.
each advert is in one category, and one or more region.
regions and categories form (each) tree structure.
assume category tree:
a
/ \
b c
/ \
d e
if any given advert is in category e. it means it is also in b and
a.
same goes for regions.
as for now we have approx. 400 categories, 1300 regions, and 100
adverts.
since checking always over the tress of categories and regions we
created acr_cache table (advert/category/region)
which stores information on all adverts and all categories and regions
this particular region is in.
plus some more information for sorting purposes.
this table is ~ 11 milion records.
now.
we query this in more or less this manner:
select advert_id from acr_cache where category_id = ? and region_id = ?
order by XXX {asc|desc} limit 20;
where XXX is one of 5 possible fields,
timestamp,
timestamp,
text,
text,
numeric
we created index on acr_cache (category_id, region_id)
and it works rather well.
usually.
if a given crossing (category + region) has small amount of ads (less
then 1) - the query is good enough (up to 300 miliseconds).
but when we enter the crossings which result in 5 ads - the query
takes up to 10 seconds.
which is almost forever.
we thought about creating indices like this:
index on acr_cache (effective_date);
where effective_dateis on of the timestamp fields.
it worked well for the crossings with lots of ads, but when we asked for
small crossing (like 1000 ads) it took 120 seconds!
it appears that postgresql was favorizing this new advert instead of
using much better index on category_id and region_id.
actually - i'm not sure what to do next.
i am even thinkinh about createing special indices (partial) for big
crossings, but that's just weird. plus the fact that already the
acr_cache vacuum time exceeds 3 hours!.
any suggestions?
hardware is dual xeon 3 ghz, 4G ram, hardware scsi raid put into raid 1.
settings in postgresql.conf:
listen_addresses = '*'
port = 5800
max_connections = 300
superuser_reserved_connections = 50
shared_buffers = 131072
work_mem = 4096
maintenance_work_mem = 65536
fsync = false
commit_delay = 100
commit_siblings = 5
checkpoint_segments = 10
effective_cache_size = 1
random_page_cost = 1.1
log_destination = 'stderr'
redirect_stderr = true
log_directory = '/home/pgdba/logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = false
log_rotation_age = 1440
log_rotation_size = 502400
log_min_duration_statement = -1
log_connections = true
log_duration = true
log_line_prefix = '[%t] [%p] [EMAIL PROTECTED] '
log_statement = 'all'
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
actual max numer of connection is 120 plus some administrative connections
(psql sessions).
postgresql version 8.0.2 on linux debian sarge.
best regards,
depesz
--
hubert lubaczewski
Network Operations Center
eo Networks Sp. z o.o.
signature.asc
Description: Digital signature
Re: [PERFORM] strategies for optimizing read on rather large tables
select advert_id from acr_cache where category_id = ? and region_id = ?
order by XXX {asc|desc} limit 20;
where XXX is one of 5 possible fields,
timestamp,
timestamp,
text,
text,
numeric
Create 5 indexes on ( category_id, region_id, a field )
where a field is one of your 5 fields.
Then write your query as :
select advert_id from acr_cache where category_id = ? and region_id = ?
order by category_id, region_id, XXX limit 20;
select advert_id from acr_cache where category_id = ? and region_id = ?
order by category_id desc, region_id desc, XXX desc limit 20;
This should put your query down to a millisecond. It will use the index
for the lookup, the sort and the limit, and hence only retrieve 20 rows
for the table. Downside is you have 5 indexes, but that's not so bad.
If your categories and regions form a tree, you should definitely use a
ltree datatype, which enables indexed operators like is contained in
which would probably allow you to reduce the size of your cache table a
lot.
we created index on acr_cache (category_id, region_id)
and it works rather well.
usually.
if a given crossing (category + region) has small amount of ads (less
then 1) - the query is good enough (up to 300 miliseconds).
but when we enter the crossings which result in 5 ads - the query
takes up to 10 seconds.
which is almost forever.
we thought about creating indices like this:
index on acr_cache (effective_date);
where effective_dateis on of the timestamp fields.
it worked well for the crossings with lots of ads, but when we asked for
small crossing (like 1000 ads) it took 120 seconds!
it appears that postgresql was favorizing this new advert instead of
using much better index on category_id and region_id.
actually - i'm not sure what to do next.
i am even thinkinh about createing special indices (partial) for big
crossings, but that's just weird. plus the fact that already the
acr_cache vacuum time exceeds 3 hours!.
any suggestions?
hardware is dual xeon 3 ghz, 4G ram, hardware scsi raid put into raid 1.
settings in postgresql.conf:
listen_addresses = '*'
port = 5800
max_connections = 300
superuser_reserved_connections = 50
shared_buffers = 131072
work_mem = 4096
maintenance_work_mem = 65536
fsync = false
commit_delay = 100
commit_siblings = 5
checkpoint_segments = 10
effective_cache_size = 1
random_page_cost = 1.1
log_destination = 'stderr'
redirect_stderr = true
log_directory = '/home/pgdba/logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = false
log_rotation_age = 1440
log_rotation_size = 502400
log_min_duration_statement = -1
log_connections = true
log_duration = true
log_line_prefix = '[%t] [%p] [EMAIL PROTECTED] '
log_statement = 'all'
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
actual max numer of connection is 120 plus some administrative
connections (psql sessions).
postgresql version 8.0.2 on linux debian sarge.
best regards,
depesz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] strategies for optimizing read on rather large tables
Without reading too hard, I suggest having a quick look at contrib/ltree
module in the PostgreSQL distribution. It may or may not help you.
Chris
hubert lubaczewski wrote:
hi
first let me draw the outline.
we have a database which stores adverts.
each advert is in one category, and one or more region.
regions and categories form (each) tree structure.
assume category tree:
a
/ \
b c
/ \
d e
if any given advert is in category e. it means it is also in b and
a.
same goes for regions.
as for now we have approx. 400 categories, 1300 regions, and 100
adverts.
since checking always over the tress of categories and regions we
created acr_cache table (advert/category/region)
which stores information on all adverts and all categories and regions
this particular region is in.
plus some more information for sorting purposes.
this table is ~ 11 milion records.
now.
we query this in more or less this manner:
select advert_id from acr_cache where category_id = ? and region_id = ?
order by XXX {asc|desc} limit 20;
where XXX is one of 5 possible fields,
timestamp,
timestamp,
text,
text,
numeric
we created index on acr_cache (category_id, region_id)
and it works rather well.
usually.
if a given crossing (category + region) has small amount of ads (less
then 1) - the query is good enough (up to 300 miliseconds).
but when we enter the crossings which result in 5 ads - the query
takes up to 10 seconds.
which is almost forever.
we thought about creating indices like this:
index on acr_cache (effective_date);
where effective_dateis on of the timestamp fields.
it worked well for the crossings with lots of ads, but when we asked for
small crossing (like 1000 ads) it took 120 seconds!
it appears that postgresql was favorizing this new advert instead of
using much better index on category_id and region_id.
actually - i'm not sure what to do next.
i am even thinkinh about createing special indices (partial) for big
crossings, but that's just weird. plus the fact that already the
acr_cache vacuum time exceeds 3 hours!.
any suggestions?
hardware is dual xeon 3 ghz, 4G ram, hardware scsi raid put into raid 1.
settings in postgresql.conf:
listen_addresses = '*'
port = 5800
max_connections = 300
superuser_reserved_connections = 50
shared_buffers = 131072
work_mem = 4096
maintenance_work_mem = 65536
fsync = false
commit_delay = 100
commit_siblings = 5
checkpoint_segments = 10
effective_cache_size = 1
random_page_cost = 1.1
log_destination = 'stderr'
redirect_stderr = true
log_directory = '/home/pgdba/logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = false
log_rotation_age = 1440
log_rotation_size = 502400
log_min_duration_statement = -1
log_connections = true
log_duration = true
log_line_prefix = '[%t] [%p] [EMAIL PROTECTED] '
log_statement = 'all'
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = true
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
actual max numer of connection is 120 plus some administrative connections
(psql sessions).
postgresql version 8.0.2 on linux debian sarge.
best regards,
depesz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] strategies for optimizing read on rather large tables
On Sat, Jun 04, 2005 at 07:17:17PM +0800, Christopher Kings-Lynne wrote: Without reading too hard, I suggest having a quick look at contrib/ltree module in the PostgreSQL distribution. It may or may not help you. acr_cache doesn't care about trees. and - since i have acr_cache - i dont have to worry about trees when selecting from acr_cache. ltree - is known to me. yet i decided not to use it to have the ability to move to another database engines without rewriting something that is havily used. depesz signature.asc Description: Digital signature
Re: [PERFORM] strategies for optimizing read on rather large tables
On Sat, Jun 04, 2005 at 01:18:04PM +0200, PFC wrote: Then write your query as : select advert_id from acr_cache where category_id = ? and region_id = ? order by category_id, region_id, XXX limit 20; this is great idea - i'll check it out definitelly. depesz signature.asc Description: Digital signature
Re: [PERFORM] strategies for optimizing read on rather large tables
select advert_id from acr_cache where category_id = ? and region_id = ? order by category_id, region_id, XXX limit 20; don't forget to mention all the index columns in the order by, or the planner won't use it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] strategies for optimizing read on rather large tables
On Sat, Jun 04, 2005 at 02:07:52PM +0200, PFC wrote: don't forget to mention all the index columns in the order by, or the planner won't use it. of course. i understand the concept. actually i find kind of ashamed i did not try it before. anyway - thanks for great tip. depesz signature.asc Description: Digital signature
