Re: [PERFORM] Plan differences

database server? Regards, Adam From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Anton Melser Sent: 01 January 2016 5:13 PM To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Plan differences Declaring new indexes

Re: [PERFORM] Plan differences

Hi, >Changing the locale to anything other than C or POSIX will > have a performance overhead. I’m pretty sure that just declaring the > locale on the indexes is just like plastering over the cracks. > > > > Is it possible to reload the database with the same locale as the

Re: [PERFORM] Plan differences

> > Declaring new indexes with COLLATE "C" and removing the old indexes fixed >> the like problem but it created a another - the > and < queries need a sort >> before passing off the the new index. Having two indexes seems to give me >> the best of both worlds, though obviously it's taking up

Re: [PERFORM] Plan differences

Hi > Does anyone have any ideas? All data are loaded into this table via copy > and no updates are done. Autovacuum settings weren't changed (and is on > both). Do I need to increase shared_buffers to half of available memory for > the planner to make certain optimisations? Anything else I'm

Re: [PERFORM] Plan differences

Hi, It is looking like some missing optimization that was removed from RC > release. > Thanks. Is there some discussion of why these optimisations were removed? I started looking at some of the more complicated queries I do and there are many occasions where there are 10-30x performance

Re: [PERFORM] Plan differences

Anton Melser writes: > I moved a DB between two "somewhat" similar Postgres installs and am > getting much worse plans on the second. The DB was dumped via pg_dump > (keeping indexes, etc.) and loaded to the new server. > [ "like 'foo%'" is not getting converted into

Re: [PERFORM] Plan differences

Jim Nasby writes: > On 12/31/15 9:02 AM, Tom Lane wrote: >> If you don't want to rebuild the whole database, you can create indexes to >> support this by declaring them with COLLATE "C", or the older way is to >> declare them with text_pattern_ops as the index opclass.

Re: [PERFORM] Plan differences

> > I'd bet your old database is in C locale and the new one is not. > Remind me never to never bet against you :-). > The LIKE optimization requires an index that's sorted according to plain > C (strcmp) rules. A regular text index will be that way only if the > database's LC_COLLATE is C. >

Re: [PERFORM] Plan differences

On 12/31/15 9:02 AM, Tom Lane wrote: If you don't want to rebuild the whole database, you can create indexes to support this by declaring them with COLLATE "C", or the older way is to declare them with text_pattern_ops as the index opclass. Do you have to do anything special in the query