[SQL] FW: Query length limitation in postgres server > 8.2.9
# min max_fsm_relations*16, 6 bytes each max_fsm_relations = 1000# min 100, ~70 bytes each fsync = false # turns forced synchronization on or off full_page_writes = off # recover from partial page writes effective_cache_size = 16384# typically 8KB each random_page_cost = 1# units are one sequential page fetch geqo = off default_statistics_target = 10 # range 1-1000 log_line_prefix = '%m' # Special values: autovacuum = on # enable autovacuum subprocess? datestyle = 'sql' lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C'# locale for number formatting lc_time = 'C' # locale for time formatting - Jacob Bresciani, Systems Administrator Advanced E-commerce Research Systems Inc. 2307-4464 Markham Street Victoria, BC CANADA V8Z 7X8 +1 250 418 5412 (mobile) +1 250 483 3271 (FAX) www.terapeak.com - eBay Marketplace Research www.aers.ca - Advanced E-commerce Analytics -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Query length limitation in postgres server > 8.2.9
127757,127758,127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,38603,68264,27187,27188,27190,27189,27191,27192,38604,112847}'::integer[]))
AND (site_id = 1))
Filter: ((buyer_cntry_id = 3) AND ((attribute_value_02 & 2::bigint) >
0))
-> Bitmap Index Scan on search_site1_2009_03_13_leaf_category_1
(cost=0.00..562.88 rows=13630 width=0) (actual time=0.961..0.961 rows=0 loops=1)
Index Cond: ((leaf_category_1 = ANY
('{4305,112798,112799,112800,112801,112802,112803,112804,112770,112771,112772,112773,112774,112775,112776,112777,112778,112779,112780,112781,112782,112783,112784,112785,112786,3641,3719,72489,72490,90843,93785,101650,91506,27252,101651,38616,72491,101652,11055,11056,27253,72492,11058,11059,1489,11321,72493,72494,90844,93786,101653,91507,27255,101654,38595,72496,11323,11324,27256,72495,11326,11327,16157,117562,127748,127749,127750,127751,127752,127753,127754,127755,127756,127757,127758,127759,127760,127761,127762,127763,91514,123436,20386,27184,68263,90850,93781,91525,27186,101646,38603,68264,27187,27188,27190,27189,27191,27192,38604,112847}'::integer[]))
AND (site_id = 1))
Total runtime: 1.048 ms
(7 rows)
Disabling sequence scan does nothing but increase cost values, I've downgraded
just postgres on one of the 8.2.13 machines to 8.2.9, pointed it at the same
PGDATA dir and it works fine again so it's not hardware or another part of the
OS being a problem. I didn't change the config so it shouldn't be that either.
8.4.0 has the same results as 8.2.13.
I'm not sure where to go from here and would appreciate any idea's you guys and
gals might have
config file contains:
listen_addresses = '*' # what IP address(es) to listen on;
port = 5432
max_connections = 62
shared_buffers = 1000 # min 16 or max_connections*2, 8KB each
work_mem = 32768# min 64, size in KB
maintenance_work_mem = 32768# min 1024, size in KB
max_fsm_pages = 12 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 1000# min 100, ~70 bytes each
fsync = false # turns forced synchronization on or off
full_page_writes = off # recover from partial page writes
effective_cache_size = 16384# typically 8KB each
random_page_cost = 1# units are one sequential page fetch
geqo = off
default_statistics_target = 10 # range 1-1000
log_line_prefix = '%m' # Special values:
autovacuum = on # enable autovacuum subprocess?
datestyle = 'sql'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C'# locale for number formatting
lc_time = 'C' # locale for time formatting
-
Jacob Bresciani, Systems Administrator
Advanced E-commerce Research Systems Inc.
2307-4464 Markham Street
Victoria, BC
CANADA V8Z 7X8
+1 250 418 5412 (mobile)
+1 250 483 3271 (FAX)
www.terapeak.com - eBay Marketplace Research
www.aers.ca - Advanced E-commerce Analytics
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Query length limitation in postgres server > 8.2.9
_14_crc, site_id) WHERE keyword_14_crc IS NOT NULL "search_site1_2009_03_13_keyword_15_crc" btree (keyword_15_crc, site_id) WHERE keyword_15_crc IS NOT NULL "search_site1_2009_03_13_keyword_16_crc" btree (keyword_16_crc, site_id) WHERE keyword_16_crc IS NOT NULL "search_site1_2009_03_13_keyword_17_crc" btree (keyword_17_crc, site_id) WHERE keyword_17_crc IS NOT NULL "search_site1_2009_03_13_keyword_18_crc" btree (keyword_18_crc, site_id) WHERE keyword_18_crc IS NOT NULL "search_site1_2009_03_13_keyword_19_crc" btree (keyword_19_crc, site_id) WHERE keyword_19_crc IS NOT NULL "search_site1_2009_03_13_leaf_category_1" btree (leaf_category_1, site_id) WHERE leaf_category_1 IS NOT NULL "search_site1_2009_03_13_seller_user_id" btree (seller_user_id, site_id) WHERE seller_user_id IS NOT NULL "search_site1_2009_03_13_upc_code" btree (upc_code, site_id) WHERE upc_code IS NOT NULL -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, July 09, 2009 11:06 AM To: Jacob Bresciani Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] FW: Query length limitation in postgres server > 8.2.9 writes: > I've simplified the query to make it easier to look at. We need to see the table/index declarations. The query by itself is just about useless. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Query length limitation in postgres server > 8.2.9
the data is insert once, read many so we should be fine on that side. I've past this on to the dev's and I'll let you know when I get feed back. Thanks for your input Tom (and the others). -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, July 09, 2009 11:29 AM To: Jacob Bresciani Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] FW: Query length limitation in postgres server > 8.2.9 writes: > leaf_category_1 Ah. So you are wishing it would use this index: "search_site1_2009_03_13_leaf_category_1" btree (leaf_category_1, site_id) WHERE leaf_category_1 IS NOT NULL If I were you I'd drop the WHERE clause, which is eliminating no index entries whatsoever (since the column is actually declared not null). What it is doing is forcing the planner to expend many cycles proving that the query's WHERE clause requires leaf_category_1 to be non-null hence the index is potentially usable. The reason recent releases are giving you trouble is that we put a limit on how many cycles we'd expend on such silliness. If you really don't want to change the schema, you could work around the issue by adding a separate "leaf_category_1 IS NOT NULL" test to the query, so that the planner can prove the index is relevant without having to grovel through hundreds of IN-list items to do it. In general, this table schema looks like somebody has drastically overengineered the index definitions with rather little understanding of what they were doing or what the performance consequences would be. I hope the table is read-mostly, because you're paying a *lot* anytime you update those indexes. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Query length limitation in postgres server > 8.2.9
Being just the server admin I'll forward this thought on to the dev's for consideration. -Original Message- From: Hartman, Matthew [mailto:matthew.hart...@krcc.on.ca] Sent: Thursday, July 09, 2009 10:58 AM To: Jacob Bresciani; pgsql-sql@postgresql.org Subject: RE: [SQL] FW: Query length limitation in postgres server > 8.2.9 > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of ja...@aers.ca > Sent: Thursday, July 09, 2009 1:53 PM > > I've simplified the query to make it easier to look at. > > This one doesn't use the index's and therefore takes about 11713ms to > return. Have you considered joining two tables, one of which is a UNION ALL sum of the identifiers? For example: Select * From MyTable Inner join ( Select 12345 as MyColumn Union all select 45678 Union all select 7890 ) as MyCriteria on MyTable.MyColumn = MyCriteria.MyColumn; Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Query length limitation in postgres server > 8.2.9
Good think I obfuscated it in one place eh :) leaf_category_1 -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, July 09, 2009 11:14 AM To: Jacob Bresciani Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] FW: Query length limitation in postgres server > 8.2.9 writes: > \d search_site1_2009_03_13 And "MyColumn1" is really which column? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how is searchable email archive on
Can anyone tell me how the searchable email archive on the postgres site is implemented. Is postgres used as part of the back end ? Has anyone tried using a postgres based solution for archiving email for regulatory purposes ? Jake Costello ---(end of broadcast)--- TIP 6: explain analyze is your friend
