Re: [PERFORM] Why is restored database faster?
On Thu, 18 Dec 2003, Shridhar Daithankar wrote: Well, then the only issue left is file sytem defragmentation. And the internal fragmentation that can be fixed with the CLUSTER command. -- /Dennis ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] why do optimizer parameters have to be set manually?
Hi, it seems to me that the optimizer parameters (like random_page_cost etc.) could easily be calculated and adjusted dynamically be the DB backend based on the planner's cost estimates and actual run times for different queries. Perhaps the developers could comment on that? I'm not sure how the parameters are used internally (apart from whatever EXPLAIN shows), but if cpu_operator_cost is the same for all operators, this should probably also be adjusted for individual operators (I suppose that is not as costly as ~*). As far as the static configuration is concerned, I'd be interested in other users' parameters and hardware configurations. Here's ours (for a write-intensive db that also performs many queries with regular expression matching): effective_cache_size = 100 # typically 8KB each #random_page_cost = 0.2 # units are one sequential page fetch cost random_page_cost = 3# units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) cpu_index_tuple_cost = 0.01 # (same) 0.1 #cpu_operator_cost = 0.0025 # (same) cpu_operator_cost = 0.025 # (same) other options: shared_buffers = 24 # 2*max_connections, min 16, typically 8KB each max_fsm_relations = 1 # min 10, fsm is free space map, ~40 bytes max_fsm_pages = 1000# min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 20 # min 10 wal_buffers = 128 # min 4, typically 8KB each sort_mem = 80 # min 64, size in KB vacuum_mem = 10 # min 1024, size in KB checkpoint_segments = 80# in logfile segments, min 1, 16MB each checkpoint_timeout = 300# range 30-3600, in seconds commit_delay = 10 # range 0-10, in microseconds commit_siblings = 5 # range 1-1000 12GB RAM, dual 2,80GHz Xeon, 6x 10K rpm disks in a RAID-5, Linux 2.4.23 with HT enabled. Regards, Marinos ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] why do optimizer parameters have to be set manually?
Marinos J. Yannikos [EMAIL PROTECTED] writes: it seems to me that the optimizer parameters (like random_page_cost etc.) could easily be calculated and adjusted dynamically be the DB backend based on the planner's cost estimates and actual run times for different queries. Perhaps the developers could comment on that? No, they are not that easy to determine. In particular I think the idea of automatically feeding back error measurements is hopeless, because you cannot tell which parameters are wrong. I'm not sure how the parameters are used internally (apart from whatever EXPLAIN shows), but if cpu_operator_cost is the same for all operators, this should probably also be adjusted for individual operators (I suppose that is not as costly as ~*). In theory perhaps, but in practice this is far down in the noise in most situations. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] why do optimizer parameters have to be set manually?
[EMAIL PROTECTED] (Marinos J. Yannikos) writes: it seems to me that the optimizer parameters (like random_page_cost etc.) could easily be calculated and adjusted dynamically be the DB backend based on the planner's cost estimates and actual run times for different queries. Perhaps the developers could comment on that? Yes, it seems like a Small Matter Of Programming. http://wombat.doc.ic.ac.uk/foldoc/foldoc.cgi?SMOP In seriousness, yes, it would seem a reasonable idea to calculate some of these values a bit more dynamically. I would be inclined to start with something that ran a workload, and provided static values based on how that workload went. That would require NO intervention inside the DB server; it could be accomplished simply by writing a database script. Feel free to contribute either a script or a backend hack... -- let name=cbbrowne and tld=libertyrms.info in String.concat @ [name;tld];; http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] is it possible to get the optimizer to use indexes with a like clause
It appears that the optimizer only uses indexes for = clause? Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] is it possible to get the optimizer to use indexes
It appears that the optimizer only uses indexes for = clause? The optimizer will used indexes for LIKE clauses, so long as the clause is a prefix search, eg: SELECT * FROM test WHERE a LIKE 'prf%'; Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] is it possible to get the optimizer to use indexes
after vacuum verbose analyze, I still get explain select * from isppm where item_upc_cd like '06038301234'; QUERY PLAN --- Seq Scan on isppm (cost=1.00..19684.89 rows=2 width=791) Filter: (item_upc_cd ~~ '06038301234'::text) (2 rows) isp=# explain select * from isppm where item_upc_cd = '06038301234'; QUERY PLAN Index Scan using isppm_x0 on isppm (cost=0.00..5.86 rows=2 width=791) Index Cond: (item_upc_cd = '06038301234'::bpchar) (2 rows) Dave On Thu, 2003-12-18 at 20:38, Christopher Kings-Lynne wrote: It appears that the optimizer only uses indexes for = clause? The optimizer will used indexes for LIKE clauses, so long as the clause is a prefix search, eg: SELECT * FROM test WHERE a LIKE 'prf%'; Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] is it possible to get the optimizer to use indexes with a like clause
[EMAIL PROTECTED] (Dave Cramer) wrote: It appears that the optimizer only uses indexes for = clause? It can use indices only if there is a given prefix. Thus: where text_field like 'A%' can use the index, essentially transforming this into the clauses where text_field = 'A' and text_field 'B'. You can't get much out of an index for where text_field like '%SOMETHING' -- (reverse (concatenate 'string moc.enworbbc @ enworbbc)) http://www3.sympatico.ca/cbbrowne/wp.html When the grammar checker identifies an error, it suggests a correction and can even makes some changes for you. -- Microsoft Word for Windows 2.0 User's Guide, p.35: ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] is it possible to get the optimizer to use indexes
On Thu, 18 Dec 2003, Dave Cramer wrote: after vacuum verbose analyze, I still get explain select * from isppm where item_upc_cd like '06038301234'; QUERY PLAN --- Seq Scan on isppm (cost=1.00..19684.89 rows=2 width=791) Filter: (item_upc_cd ~~ '06038301234'::text) (2 rows) IIRC, the other limitation is that it only does so in C locale due to wierdnesses in other locales. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
