Re: [PERFORM] How to SELECT

2010-03-10 Thread Angayarkanni
Yes I got Yaar Thanks a lot !!! (Dave Crooke,A. Kretschmer ) Regards, Angayarkanni Kajendran On Thu, Mar 11, 2010 at 12:44 PM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > In response to Angayarkanni : > > Hi, > > > > I am the beginner of pgpsql > > > > > > I need to select

Re: [PERFORM] How to SELECT

2010-03-10 Thread Dave Crooke
Hi there This list is for performance tuning questions related to PostgreSQL ... your question is a general SQL syntax issue. Also, it's not quite clear from your message exactly what you are trying to do - it's better to post example table schemas. At a guess, I think you might want: select 1,

Re: [PERFORM] How to SELECT

2010-03-10 Thread A. Kretschmer
In response to Angayarkanni : > Hi, > > I am the beginner of pgpsql > >   > I need to select from two tables say T1,T2 by UNION > > when the value is from T1 the output should by a 1 and > when the value from T2 the out put should be 2 > > when T1 Hits values should be 1 and when T2 hits the va

[PERFORM] How to SELECT

2010-03-10 Thread Angayarkanni
Hi, I am the beginner of pgpsql I need to select from two tables say T1,T2 by UNION when the value is from T1 the output should by a 1 and when the value from T2 the out put should be 2 when T1 Hits values should be 1 and when T2 hits the value should be 2 or when we use COALESCE(is exits v

Re: [PERFORM] shared_buffers advice

2010-03-10 Thread Ben Chobot
On Mar 10, 2010, at 6:22 PM, Paul McGarry wrote: > Hi there, > > I'm after a little bit of advice on the shared_buffers setting (I have > read the various docs on/linked from the performance tuning wiki page, > some very helpful stuff there so thanks to those people). > > I am setting up a 64bit

[PERFORM] shared_buffers advice

2010-03-10 Thread Paul McGarry
Hi there, I'm after a little bit of advice on the shared_buffers setting (I have read the various docs on/linked from the performance tuning wiki page, some very helpful stuff there so thanks to those people). I am setting up a 64bit Linux server running Postgresql 8.3, the server has 64gigs of m

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread Robert Haas
On Wed, Mar 10, 2010 at 5:37 PM, Tom Lane wrote: > Robert Haas writes: >> It does seem like once the materialize step is done we could notice >> that the tuplestore is empty and, given that uses no outer variables >> or parameters and therefore will never be re-executed, we could skip >> the rest

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread Tom Lane
Robert Haas writes: > It does seem like once the materialize step is done we could notice > that the tuplestore is empty and, given that uses no outer variables > or parameters and therefore will never be re-executed, we could skip > the rest of the index scan. Yeah, the same thing occurred to me

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread Robert Haas
On Wed, Mar 10, 2010 at 6:04 AM, Yeb Havinga wrote: > sverha...@wps-nl.com wrote: >> >> > Thanks - I'm sorry that I was not more specific earlier, but what would >> > be *really* helpful is the output of explain analyze, since that also >> > shows actual time, # rows and # loops of the inner nestl

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Benoit Delbosc
Tom Lane a écrit : Benoit Delbosc writes: I am trying to understand why inside an EXISTS clause the query planner does not use the index: I'm not sure this plan is as bad as all that. The key point is that the planner is expecting 52517 rows that match that users_md5 value (and the true nu

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread Harald Fuchs
In article , sverha...@wps-nl.com writes: > SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON > eventType_id= > events_event_types.id WHERE severity=70 AND (eventType_id IN (71)) ORDER BY > datetime DESC LIMIT 50; > Now I have at least two possibilities: > - Implementing the dummy

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Tom Lane
Benoit Delbosc writes: > I am trying to understand why inside an EXISTS clause the query planner > does not use the index: I'm not sure this plan is as bad as all that. The key point is that the planner is expecting 52517 rows that match that users_md5 value (and the true number is evidently

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Benoit Delbosc
Yeb Havinga a écrit : Yeb Havinga wrote: Kenneth Marshall wrote: EXISTS matches NULLs too and since they are not indexed a sequential scan is needed to check for them. Try using IN instead. This is nonsense in more than one way. Hit ctrl-return a bit too slow - exists does not match null bu

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Yeb Havinga
Yeb Havinga wrote: Kenneth Marshall wrote: EXISTS matches NULLs too and since they are not indexed a sequential scan is needed to check for them. Try using IN instead. This is nonsense in more than one way. Hit ctrl-return a bit too slow - exists does not match null but a set of records, th

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Yeb Havinga
Kenneth Marshall wrote: EXISTS matches NULLs too and since they are not indexed a sequential scan is needed to check for them. Try using IN instead. This is nonsense in more than one way. regards Yeb Havinga -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To m

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Grzegorz Jaśkiewicz
try JOINs...

Re: [PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Kenneth Marshall
EXISTS matches NULLs too and since they are not indexed a sequential scan is needed to check for them. Try using IN instead. Cheers, Ken On Wed, Mar 10, 2010 at 02:26:20PM +0100, Benoit Delbosc wrote: > Hi all, > > I am trying to understand why inside an EXISTS clause the query planner > does n

[PERFORM] Bad query plan inside EXISTS clause

2010-03-10 Thread Benoit Delbosc
Hi all, I am trying to understand why inside an EXISTS clause the query planner does not use the index: EXPLAIN ANALYZE SELECT 1 WHERE EXISTS (SELECT 1 FROM read_acls_cache WHERE users_md5 = '9bc9012eb29c0bb2ae3cc7b5e78c2acf'); QUERY PLAN

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread Yeb Havinga
sverha...@wps-nl.com wrote: > Thanks - I'm sorry that I was not more specific earlier, but what would > be *really* helpful is the output of explain analyze, since that also > shows actual time, # rows and # loops of the inner nestloop. No problem at all. EXPLAIN ANALYZE SELECT * FROM events_

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread sverhagen
> Thanks - I'm sorry that I was not more specific earlier, but what would > be *really* helpful is the output of explain analyze, since that also > shows actual time, # rows and # loops of the inner nestloop. No problem at all. EXPLAIN ANALYZE SELECT * FROM events_events LEFT OUTER JOIN events_

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread Yeb Havinga
sverha...@wps-nl.com wrote: Hi, EXPLAIN SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=20 AND (eventType_id IN (71)) ORDER BY datetime DESC limit 50; QUERY PLAN

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread sverhagen
Hi, EXPLAIN SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=20 AND (eventType_id IN (71)) ORDER BY datetime DESC limit 50; QUERY PLAN ---

Re: [PERFORM] Strange workaround for slow query

2010-03-10 Thread Yeb Havinga
Hello Sander, Can you post the explain plan output of these queries? SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON eventType_id=events_event_types.id WHERE severity=20 AND (eventType_id IN (71)) ORDER BY datetime DESC limit 50; SELECT * FROM events_events LEFT OUTER JOIN

[PERFORM] Strange workaround for slow query

2010-03-10 Thread Kees van Dieren
Hi folks, We have two related tables with event types and events. We query for a join between these two tables and experience that, when there is an to-be-expected very small result set, this query performs particularly poor. Understanding in this matter would be appreciated. SELECT * from event

[PERFORM] Strange workaround for slow query

2010-03-10 Thread sverhagen
Hi group, We have two related tables with event types and events. We query for a join between these two tables and experience that, when there is an to-be-expected very small result set, this query performs particularly poor. Understanding in this matter would be appreciated. SELECT * from eve