Re: [PERFORM] Which is better Index

2011-04-05 Thread Greg Smith
On 04/05/2011 06:26 AM, Adarsh Sharma wrote: CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id, clause_id, sentence_id); or CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id); CREATE INDEX idx_svo2_id_dummy1 ON svo2 USING btree (clause_id); CREATE INDEX idx_svo2_id_dumm

Re: [PERFORM] Partial index slower than regular index

2011-04-05 Thread Tom Lane
Thom Brown writes: > The index doesn't get used. There's probably a logical explanation, > which is what I'm curious about. Er ... it's broken? It looks like the index predicate expression isn't getting the right collation assigned, so predtest.c decides the query doesn't imply the index's pred

Re: [PERFORM] Partial index slower than regular index

2011-04-05 Thread Mark Kirkwood
On 06/04/11 11:40, Mark Kirkwood wrote: On 06/04/11 11:31, Scott Marlowe wrote: On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown wrote: I'm using 9.1dev. SNIP DROP INDEX indextest_stuff; CREATE INDEX indextest_stuff ON indextest(stuff) WHERE stuff = 'bark'; postgres=# explain analyze select * f

Re: [PERFORM] Partial index slower than regular index

2011-04-05 Thread Mark Kirkwood
On 06/04/11 11:31, Scott Marlowe wrote: On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown wrote: I'm using 9.1dev. SNIP DROP INDEX indextest_stuff; CREATE INDEX indextest_stuff ON indextest(stuff) WHERE stuff = 'bark'; postgres=# explain analyze select * from indextest where stuff = 'bark';

Re: [PERFORM] Partial index slower than regular index

2011-04-05 Thread Scott Marlowe
On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown wrote: > I'm using 9.1dev. SNIP > DROP INDEX indextest_stuff; > > CREATE INDEX indextest_stuff ON indextest(stuff) WHERE stuff = 'bark'; > > postgres=# explain analyze select * from indextest where stuff = 'bark'; >                                      

Re: [PERFORM] Partial index slower than regular index

2011-04-05 Thread Kenneth Marshall
On Tue, Apr 05, 2011 at 11:35:29PM +0100, Thom Brown wrote: > I'm using 9.1dev. > > Could someone explain the following behaviour? > > -- create a test table > CREATE TABLE indextest (id serial, stuff text); > > -- insert loads of values with intermittent sets of less common values > INSERT INTO

[PERFORM] Partial index slower than regular index

2011-04-05 Thread Thom Brown
I'm using 9.1dev. Could someone explain the following behaviour? -- create a test table CREATE TABLE indextest (id serial, stuff text); -- insert loads of values with intermittent sets of less common values INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,100); INSERT INTO

Re: [PERFORM] Intel SSDs that may not suck

2011-04-05 Thread Merlin Moncure
On Mon, Apr 4, 2011 at 8:26 PM, Greg Smith wrote: > On 03/28/2011 04:21 PM, Greg Smith wrote: >> >> Today is the launch of Intel's 3rd generation SSD line, the 320 series. >>  And they've finally produced a cheap consumer product that may be useful >> for databases, too!  They've put 6 small capac

Re: [PERFORM] Which is better Index

2011-04-05 Thread Chetan Suttraway
On Tue, Apr 5, 2011 at 3:56 PM, Adarsh Sharma wrote: > Dear all, > > I want to clear my doubts regarding creating several single or a > multi-column indexes. > My table schema is :- > CREATE TABLE svo2( svo_id bigint NOT NULL DEFAULT > nextval('svo_svo_id_seq'::regclass), doc_id integer, sent

Re: [PERFORM] Postgres Performance Tuning

2011-04-05 Thread Scott Marlowe
On Tue, Apr 5, 2011 at 7:20 AM, Adarsh Sharma wrote: > Scott Marlowe wrote: > > On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma > wrote: > > > [root@s8-mysd-2 ~]# free -m >            total       used       free     shared    buffers     cached > Mem:         15917      15826         90          0

Re: [PERFORM] Postgres Performance Tuning

2011-04-05 Thread Adarsh Sharma
Scott Marlowe wrote: On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma wrote: [root@s8-mysd-2 ~]# free -m total used free sharedbuffers cached Mem: 15917 15826 90 0101 15013 -/+ buffers/cache:711 15205 Swa

Re: [PERFORM] Postgres Performance Tuning

2011-04-05 Thread Scott Marlowe
On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma wrote: > > [root@s8-mysd-2 ~]# free -m >            total       used       free     shared    buffers     cached > Mem:         15917      15826         90          0        101      15013 > -/+ buffers/cache:        711      15205 > Swap:        16394

Re: [PERFORM] Postgres Performance Tuning

2011-04-05 Thread Ákos Gábriel
On Apr 5, 2011, at 9:33 AM, Adarsh Sharma wrote: > Now I have to start more queries on Database Server and issue new connections > after some time. Why the cached memory is not freed. It's freed on-demand. > Flushing the cache memory is needed & how it could use so much if I set Why would forc

[PERFORM] Which is better Index

2011-04-05 Thread Adarsh Sharma
Dear all, I want to clear my doubts regarding creating several single or a multi-column indexes. My table schema is :- CREATE TABLE svo2( svo_id bigint NOT NULL DEFAULT nextval('svo_svo_id_seq'::regclass), doc_id integer, sentence_id integer, clause_id integer, negation integer, subject

Re: [PERFORM] Postgres Performance Tuning

2011-04-05 Thread Adarsh Sharma
Hi, Good Morning To All of You. Yesterday I had some research on my problems. As Scott rightly suggest me to have pre information before posting in the list, I aggreed to him. Here is my first doubt , that I explain as: My application makes several connections to Database Server & done their