[PERFORM] choosing fillfactor
I've tried searching the documentation to answer this question but could not find anything. When trying to choose the optimal fillfactor for an index, what is important the number of times the row is updated or the column indexed upon is updated? In my case each row is updated on average about 5 times but for some of the columns with indexes don't change after insertion ever. thanks for any advice ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [JDBC] does prepareThreshold work? forced to use old driver
Thank you! setting the protocolVersion=2 works with the newer driver. I'm still puzzled as to why the prepareThreshold=0 doesn't force the replan though. On 2/26/07, Dave Cramer <[EMAIL PROTECTED]> wrote: On 26-Feb-07, at 11:12 AM, Gene wrote: > hi! > > I've been having some serious performance issues with > postgresql8.2/hibernate/jdbc due to postgres reusing bad cached query > plans. It doesn't look at the parameter values and therefore does not > use any partial indexes. > > After trying to set prepareThreshold=0 in the connection string which > didnt work, even modifying the jdbc driver and forcing it to 0 and not > working I realized that it must be being ignored. After giving up > pretty much I tried a much older driver which doesn't use server > prepared statements at all the problem has gone away and it is once > again using the partial indexes. How can I get this to work properly > on the new jdbc driver? I don't really like having to use a 2 year old > driver to get good performance as you can imagine :) > > Could someone point me to a jdbc src file where I could just disable > server-side prepared statements entirely? > you can just add protocolVersion=2 to the url and it will not use prepared statements. setting prepareThreshold=0 just tells it not to use named statements. It will still use statements but won't cache them. Are you sure the problem is with cached statements ? There are issues where prepared statements won't use the index if you don't use the correct type. Dave > -- > thanks, G > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Gene Hart cell: 443-604-2679 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] does prepareThreshold work? forced to use old driver
hi! I've been having some serious performance issues with postgresql8.2/hibernate/jdbc due to postgres reusing bad cached query plans. It doesn't look at the parameter values and therefore does not use any partial indexes. After trying to set prepareThreshold=0 in the connection string which didnt work, even modifying the jdbc driver and forcing it to 0 and not working I realized that it must be being ignored. After giving up pretty much I tried a much older driver which doesn't use server prepared statements at all the problem has gone away and it is once again using the partial indexes. How can I get this to work properly on the new jdbc driver? I don't really like having to use a 2 year old driver to get good performance as you can imagine :) Could someone point me to a jdbc src file where I could just disable server-side prepared statements entirely? -- thanks, G ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Areca 1260 Performance
I'm building a SuperServer 6035B server (16 scsi drives). My schema has basically two large tables (million+ per day) each which are partitioned daily, and queried independently of each other. Would you recommend a raid1 system partition and 14 drives in a raid 10 or should i create separate partitions/tablespaces for the two large tables and indexes? Thanks Gene On 12/7/06, Shane Ambler <[EMAIL PROTECTED]> wrote: >> One thing that is clear from what you've posted thus far is that you >> are going to needmore HDs if you want to have any chance of fully >> utilizing your Areca HW. > Do you know off hand where I might find a chassis that can fit 24[+] > drives? The last chassis we ordered was through Supermicro, and the > largest they carry fits 16 drives. Chenbro has a 24 drive case - the largest I have seen. It fits the big 4/8 cpu boards as well. http://www.chenbro.com/corporatesite/products_01features.php?serno=43 -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Gene Hart cell: 443-604-2679
Re: [PERFORM] Slow functional indexes?
I have a varchar field which is most commonly queried like "someField like '%abcd'". Realizing that it wouldn't be able to use an index for this type of query I created a reverse() function and an index using the function reverse(someField) so that the query would be performed as "reverse(someField) like reverse('%abcd')". When I looked at the query plan it seemed like it was using the new reverse index properly but also seemed to run slower. Would this explain these bazaar results? I have since gone back to the method without using the reverse function. Thanks
On 11/5/06, Tom Lane <[EMAIL PROTECTED]> wrote:
Stuart Bishop <[EMAIL PROTECTED]> writes:> Here is a minimal test case that demonstrates the issue. Can anyone else> reproduce these results? Of the four EXPLAIN ANALYZE SELECT statements at
> the end, the one that orders by a user created IMMUTABLE stored procedure is> consistently slower than the other three variants.Wow, interesting. I'm surprised we never realized this before, but
here's the deal: the generated plan computes the ORDER BY expressionseven if we end up not needing them because the ordering is created byan indexscan rather than an explicit sort step. (Such a sort step would
of course need the values as input.) So the differential you're seeingrepresents the time for all those useless evaluations of the function.The difference in the estimated cost comes from that too --- the code
doing the estimation can see perfectly well that there's an extrafunction call in the plan ...Not sure whether there's a simple way to fix this; it might take somenontrivial rejiggering in the planner. Or maybe not, but I don't have
any cute ideas about it at the moment.I wonder whether there are any other cases where we are doing uselesscomputations of resjunk columns?regards, tom lane---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
-- Gene Hartcell: 443-604-2679
[PERFORM] Update INSERT RULE while running for Partitioning
I'm using PostgreSQL 8.1.4 in a Hibernate Application and I am attempting to use partitioning via Inherited tables. At first I was going to create a rule per sub-table based on a date range, but found out with multiple rules postgres will only return the affected-row count on the last rule which gives Hibernate problems. So now I'm thinking the way to do it is just have one rule at a time and when I want to start appending data to a new partition, just change the rule on the parent table and also update the constraint on the last table to reflect the date ranges contained so that constraint_exclusion will work. this should perform better also. For instance Starting off with: Parent (Rule on insert instead insert into Child2) Child1 (Constraint date <= somedate1) Child2 (Constraint date > somedate1) Now I want to create another Partition: Create Table Child3 BEGIN Update Parent Rule( instead insert into Child3) somedate2 = max(date) from Child2 Update Child2 Constraint( date > somedate1 AND date <= somedate2 ) Set Constraint Child3 (date > somedate2) END Which ends up with: Parent (Rule on insert instead insert into Child2) Child1 (Constraint date <= somedate1) Child2 (Constraint date > somedate1 AND date <= somedate2) Child3 (Constraint date > somedate2)Anyone else tried this or expect it to work consistently (without stopping db)? Is it possible that there could be a race condition for the insertion and constraints or will the transaction prevent that from occurring? I've done some testing and it seems to work but I could just get lucky so far and not lose any data :) Thanks for any help,Gene
Re: [PERFORM] optimizing LIKE '%2345' queries
Thanks for the suggestion. Actually I went ahead and created a reverse
function using plpgsql, created an index using reverse column and now
my queries use "where reverse(column) like reverse('%2345') and it's
using the index like i hoped it would! Now if I could figure out how
to optimize like '%2345%' queries. I don't want to create many
indexes though the table is very write heavy.
> Is the only way to create a reverse function and create an index using
> the reverse function and modify queries to use:
>
> where reverse(column) like reverse('%2345') ?
Hmm.. interesting.
If (and only if) the records stored in "column" column have fixed
length (say, all are 50 characters in length) you could create and index
on, say, substring(column,45,50), and use this in the WHERE clauses in
your queries.
Or if the length of those records is not the same maybe it is
feasible to create an ondex on substring(column, length(column)-5,
length(column)).
--
Any views or opinions presented within this e-mail are solely those of
the author and do not necessarily represent those of any company, unless
otherwise expressly stated.
---(end of broadcast)---
TIP 6: explain analyze is your friend
[PERFORM] optimizing LIKE '%2345' queries
Is there any way to create a reverse index on string columns so that
queries of the form:
where column like '%2345';
can use an index and perform as fast as searching with like '2345%'?
Is the only way to create a reverse function and create an index using
the reverse function and modify queries to use:
where reverse(column) like reverse('%2345') ?
thanks
--
Eugene Hart
Cell: 443-604-2679
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
