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 || '.%'); QUERY

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: Please find the details of table description test=# \d xxx Table public.xxx Column|Type | Modifiers --+-+--- crawler_id

[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

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 want

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'

[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

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 (about 2

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 rdyf4e...@gmail.com 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

Re: [PERFORM] fast read of binary data

2012-11-12 Thread Merlin Moncure
On Mon, Nov 12, 2012 at 4:45 AM, Eildert Groeneveld eildert.groenev...@fli.bund.de 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

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 ashuco...@gmail.com 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 *

[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,

Re: [PERFORM] performance regression with 9.2

2012-11-12 Thread Tom Lane
Dave Cramer p...@fastcrypt.com 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?

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 t...@sss.pgh.pa.us wrote: Dave Cramer p...@fastcrypt.com writes: This query is a couple orders of magnitude slower the first result is 9.2.1,

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

Re: [PERFORM] performance regression with 9.2

2012-11-12 Thread Tom Lane
Willem Leenen willem_lee...@hotmail.com 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

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 will