Re: [PERFORM] Why is restored database faster?

2003-12-18 Thread Dennis Bjorklund
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

[PERFORM] why do optimizer parameters have to be set manually?

2003-12-18 Thread Marinos J. Yannikos
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 t

Re: [PERFORM] why do optimizer parameters have to be set manually?

2003-12-18 Thread Tom Lane
"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

Re: [PERFORM] why do optimizer parameters have to be set manually?

2003-12-18 Thread Christopher Browne
[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 dev

[PERFORM] is it possible to get the optimizer to use indexes with a like clause

2003-12-18 Thread Dave Cramer
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

2003-12-18 Thread Christopher Kings-Lynne
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 'kil

Re: [PERFORM] is it possible to get the optimizer to use indexes

2003-12-18 Thread Dave Cramer
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) Fil

Re: [PERFORM] is it possible to get the optimizer to use indexes with a like clause

2003-12-18 Thread Christopher Browne
[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

Re: [PERFORM] is it possible to get the optimizer to use indexes

2003-12-18 Thread Stephan Szabo
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 (cos

Re: [PERFORM] is it possible to get the optimizer to use indexes

2003-12-18 Thread Tom Lane
Dave Cramer <[EMAIL PROTECTED]> writes: > after vacuum verbose analyze, I still get [a seqscan] The other gating factor is that you have to have initdb'd in C locale. Non-C locales tend to use wild and wooly sort orders that are not compatible with what LIKE needs. regards