On Fri, 2003-10-03 at 17:50, Tom Lane wrote:
ps*******@freeuk.com writes: I am running a SELECT to get all tuples within a given date range. This
query is much slwoer than i expected - am i missing something?
Well, it seems to be running at about 5 msec/row, which would be quite
respectable if each fetch required another disk seek. I'm wondering why
you are (apparently) not managing to get more than one row per page
fetched. What are your configuration settings --- particularly
shared_buffers? Could we see the output of VACUUM VERBOSE for this
table?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
Tom,
Thanks for your comments.
Here is the information you requested:
The date column is in the public.meta table.
public.data contains bodies of text to be searched.
DATA:
Lines from postgresql.conf that don't start with a '#':
tcpip_socket = true
shared_buffers = 126976 #992 MB
sort_mem = 36864 #36 MB
vacuum_mem = 73696 #72 MB
stats_start_collector = true
stats_command_string = true
stats_row_level = true
stats_block_level = 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'
testdb=# VACUUM VERBOSE;
INFO: --Relation pg_catalog.pg_conversion--
INFO: Pages 2: Changed 0, Empty 0; Tup 114: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_depend--
INFO: Pages 22: Changed 0, Empty 0; Tup 2947: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: --Relation pg_catalog.pg_attrdef--
INFO: Pages 1: Changed 0, Empty 0; Tup 6: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16384--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_constraint--
INFO: Pages 1: Changed 0, Empty 0; Tup 20: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16386--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_database--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 3.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1262--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_description--
INFO: Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: --Relation pg_toast.pg_toast_16416--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_group--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1261--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_proc--
INFO: Pages 59: Changed 0, Empty 0; Tup 1492: Vac 0, Keep 0, UnUsed
179.
Total CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: --Relation pg_toast.pg_toast_1255--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_rewrite--
INFO: Pages 4: Changed 0, Empty 0; Tup 27: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_16410--
INFO: Pages 4: Changed 0, Empty 0; Tup 16: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_type--
INFO: Pages 4: Changed 0, Empty 0; Tup 190: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_attribute--
INFO: Pages 18: Changed 0, Empty 0; Tup 1062: Vac 0, Keep 0, UnUsed 7.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_class--
INFO: Pages 5: Changed 0, Empty 0; Tup 175: Vac 0, Keep 0, UnUsed 71.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_inherits--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_index--
INFO: Pages 3: Changed 0, Empty 0; Tup 94: Vac 0, Keep 0, UnUsed 1.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_operator--
INFO: Pages 14: Changed 0, Empty 0; Tup 643: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_opclass--
INFO: Pages 1: Changed 0, Empty 0; Tup 51: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_am--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_amop--
INFO: Pages 1: Changed 0, Empty 0; Tup 180: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_amproc--
INFO: Pages 1: Changed 0, Empty 0; Tup 57: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_language--
INFO: Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 3.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_largeobject--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_shadow--
INFO: Pages 1: Changed 0, Empty 0; Tup 2: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_1260--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_aggregate--
INFO: Pages 1: Changed 0, Empty 0; Tup 60: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_trigger--
INFO: Pages 1: Changed 0, Empty 0; Tup 26: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_listener--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_cast--
INFO: Pages 2: Changed 0, Empty 0; Tup 174: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_namespace--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_catalog.pg_statistic--
INFO: Pages 17: Changed 0, Empty 0; Tup 164: Vac 0, Keep 0, UnUsed 320.
Total CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO: --Relation pg_toast.pg_toast_16408--
INFO: Pages 5: Changed 0, Empty 0; Tup 6: Vac 0, Keep 0, UnUsed 15.
Total CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO: --Relation public.meta--
INFO: Pages 685043: Changed 0, Empty 8; Tup 5999170: Vac 0, Keep 0,
UnUsed 5999170.
Total CPU 18.06s/3.61u sec elapsed 612.91 sec.
INFO: --Relation pg_toast.pg_toast_16989--
INFO: Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.data--
INFO: Pages 1359266: Changed 0, Empty 24; Tup 5999174: Vac 0, Keep 0,
UnUsed 1595705.
Total CPU 33.74s/6.64u sec elapsed 1436.04 sec.
INFO: --Relation pg_toast.pg_toast_16979--
INFO: Pages 1154272: Changed 0, Empty 9; Tup 4454208: Vac 0, Keep 0,
UnUsed 1074682.
Total CPU 28.98s/5.65u sec elapsed 1159.64 sec.
INFO: --Relation public.sys_id_ng_id_link--
INFO: Pages 76254: Changed 0, Empty 1; Tup 11960406: Vac 0, Keep 0,
UnUsed 10064.
Total CPU 1.96s/1.10u sec elapsed 97.64 sec.
INFO: --Relation public.src_grp--
INFO: Pages 202: Changed 0, Empty 0; Tup 23501: Vac 0, Keep 0, UnUsed
0.
Total CPU 0.00s/0.00u sec elapsed 0.24 sec.
INFO: --Relation pg_toast.pg_toast_17009--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.meta2--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_17041--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.data2--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_17031--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.src_grp2--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation pg_toast.pg_toast_17061--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: --Relation public.data2_group_link--
INFO: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend