Re: [PERFORM] PostreSQL v9.2 uses a lot of memory in Windows XP

2012-11-12 Thread Craig Ringer
On 11/12/2012 10:17 PM, Wu Ming wrote: > See this screenshot link from the Process Explorer: > > http://i45.tinypic.com/vr4t3b.png That looks pretty reasonable to me. The "virtual size" includes the shared memory segment, so the per-process use is actually much lower than it looks. The real use wi

Re: [PERFORM] performance regression with 9.2

2012-11-12 Thread Tom Lane
Willem Leenen writes: > To me, i see a mismatch between the optimizer and the actual records > retrieved in the fast SQL as well, so plan instability is a realistic > scenario. Well, the rowcount estimates for a recursive union are certainly pretty bogus, but those are the same either way. The r

Re: [PERFORM] performance regression with 9.2

2012-11-12 Thread Willem Leenen
Hello Tom, Could you elaborate on this? I'm trying to learn the explain plans of postgresql and i would like to know if we're looking at the same clue's. To me, i see a mismatch between the optimizer and the actual records retrieved in the fast SQL as well, so plan instability is a realistic s

Re: [PERFORM] performance regression with 9.2

2012-11-12 Thread Dave Cramer
Tom, Will try to get one ASAP. Dave Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Mon, Nov 12, 2012 at 3:43 PM, Tom Lane wrote: > Dave Cramer writes: > > This query is a couple orders of magnitude slower the first result is > > 9.2.1, the second 9.1 > > Hm, the plan

Re: [PERFORM] performance regression with 9.2

2012-11-12 Thread Tom Lane
Dave Cramer writes: > This query is a couple orders of magnitude slower the first result is > 9.2.1, the second 9.1 Hm, the planner's evidently doing the wrong thing inside the recursive union, but not obvious why. Can you extract a self-contained test case? regards, tom

[PERFORM] performance regression with 9.2

2012-11-12 Thread Dave Cramer
This query is a couple orders of magnitude slower the first result is 9.2.1, the second 9.1 =# explain analyze SELECT note_sets."id" AS t0_r0, note_sets."note_set_source_id" AS t0_r1, note_sets."parent_id" AS t0_r2, note_sets."business_entity_id" AS t0_r3, note_sets."created_at" AS t0_r4, note_set

Re: [PERFORM] Planner sometimes doesn't use a relevant index with IN (subquery) condition

2012-11-12 Thread Rafał Rzepecki
This indeed works around the issue. Thanks! On Mon, Nov 12, 2012 at 9:53 AM, ashutosh durugkar wrote: > Hey Rafal, > > >>SELECT * FROM (SELECT run_id, utilization FROM stats) AS s WHERE > run_id IN (SELECT run_id FROM runs WHERE server_id = 515); > > could you try this: > > > SELECT * FROM (SELEC

Re: [PERFORM] fast read of binary data

2012-11-12 Thread Merlin Moncure
On Mon, Nov 12, 2012 at 4:45 AM, Eildert Groeneveld wrote: > Dear All > > I am currently implementing using a compressed binary storage scheme > genotyping data. These are basically vectors of binary data which may be > megabytes in size. > > Our current implementation uses the data type bit varyi

Re: [PERFORM] PostreSQL v9.2 uses a lot of memory in Windows XP

2012-11-12 Thread Merlin Moncure
On Mon, Nov 12, 2012 at 8:17 AM, Wu Ming wrote: > Hi, > > I had installed postgreSQL v9.2 in Windows XP SP3. > > My PC specs: > Processor: Pentium Dual Core 2.09 GHz > RAM: 2GB > > The postgreSQL is run as windows service (manual). > > The problem is the postgreSQL service uses a lot of memory and

Re: [PERFORM] PostreSQL v9.2 uses a lot of memory in Windows XP

2012-11-12 Thread Albe Laurenz
Wu Ming wrote: > I had installed postgreSQL v9.2 in Windows XP SP3. > > My PC specs: > Processor: Pentium Dual Core 2.09 GHz > RAM: 2GB > > The postgreSQL is run as windows service (manual). > > The problem is the postgreSQL service uses a lot of memory and lags > the OS if running in long time

[PERFORM] PostreSQL v9.2 uses a lot of memory in Windows XP

2012-11-12 Thread Wu Ming
Hi, I had installed postgreSQL v9.2 in Windows XP SP3. My PC specs: Processor: Pentium Dual Core 2.09 GHz RAM: 2GB The postgreSQL is run as windows service (manual). The problem is the postgreSQL service uses a lot of memory and lags the OS if running in long time (about 2 hours or more) so I h

Re: [PERFORM] fast read of binary data

2012-11-12 Thread Arjen van der Meijden
On 12-11-2012 11:45, Eildert Groeneveld wrote: Dear All I am currently implementing using a compressed binary storage scheme genotyping data. These are basically vectors of binary data which may be megabytes in size. Our current implementation uses the data type bit varying. Wouldn't 'bytea'

Re: [PERFORM] fast read of binary data

2012-11-12 Thread Albe Laurenz
Eildert Groeneveld wrote: > I am currently implementing using a compressed binary storage scheme > genotyping data. These are basically vectors of binary data which may be > megabytes in size. > > Our current implementation uses the data type bit varying. > > What we want to do is very simple: we

[PERFORM] fast read of binary data

2012-11-12 Thread Eildert Groeneveld
Dear All I am currently implementing using a compressed binary storage scheme genotyping data. These are basically vectors of binary data which may be megabytes in size. Our current implementation uses the data type bit varying. What we want to do is very simple: we want to retrieve such record

Re: [PERFORM] Index is not using

2012-11-12 Thread Albe Laurenz
K P Manoj wrote: > Please find the details of table description > > test=# \d xxx >Table "public.xxx" > Column|Type | Modifiers > --+-+--- > crawler_id

Re: [PERFORM] Index is not using

2012-11-12 Thread K P Manoj
Hi Albe, Thank you for your reply , Please find the details of table description test=# \d xxx Table "public.xxx" Column|Type | Modifiers --+-+--- crawler_id

Re: [PERFORM] Index is not using

2012-11-12 Thread Albe Laurenz
K P Manoj wrote: > I am facing query performance in one of my testing server. > How i can create index with table column name ? > EXPLAIN select xxx.* from xxx xxx where exists (select 1 from tmp where mdc_domain_reverse like > xxx.reverse_pd || '.%'); > QUER