Re: [PERFORM] Query tuning help

> > >  Hash Join  (cost=154.46..691776.11 rows=10059626 width=100) (actual > time=5.191..37551.360 rows=10063432 loops=1) >    Hash Cond: (a.order_id = > o.order_id) >    ->  Seq Scan on cust_acct a  (cost=0.00..540727.26 rows=10059626 > width=92) (actual time=0.022..18987.095 rows=10063432 > loops

Re: [PERFORM] Query tuning help

On 10/11/2011 12:03 PM, Szymon Guz wrote: On 11 October 2011 19:52, CS DBA > wrote: Hi all ; I'm trying to tune a difficult query. I have 2 tables: cust_acct (9million rows) cust_orders (200,000 rows) Here's the query: SELECT

Re: [PERFORM] Query tuning help

On 10/11/2011 12:02 PM, Pavel Stehule wrote: Hello please, send EXPLAIN ANALYZE output instead. Regards Pavel Stehule 2011/10/11 CS DBA: Hi all ; I'm trying to tune a difficult query. I have 2 tables: cust_acct (9million rows) cust_orders (200,000 rows) Here's the query: SELECT a.ac

Re: [PERFORM] Query tuning help

On 11 October 2011 19:52, CS DBA wrote: > Hi all ; > > I'm trying to tune a difficult query. > > I have 2 tables: > cust_acct (9million rows) > cust_orders (200,000 rows) > > Here's the query: > > SELECT > a.account_id, a.customer_id, a.order_id, a.primary_contact_id, > a.status, a.cust

Re: [PERFORM] Query tuning help

Hello please, send EXPLAIN ANALYZE output instead. Regards Pavel Stehule 2011/10/11 CS DBA : > Hi all ; > > I'm trying to tune a difficult query. > > I have 2 tables: > cust_acct (9million rows) > cust_orders (200,000 rows) > > Here's the query: > > SELECT >     a.account_id, a.customer_id, a.o

[PERFORM] Query tuning help

Hi all ; I'm trying to tune a difficult query. I have 2 tables: cust_acct (9million rows) cust_orders (200,000 rows) Here's the query: SELECT a.account_id, a.customer_id, a.order_id, a.primary_contact_id, a.status, a.customer_location_id, a.added_date, o.agent_id, p.order_location

Re: [PERFORM] query tuning help

On 06/14/2010 05:41 AM, AI Rumman wrote: Can any one please help me in tuning the query? explain select * from (select * from crmentity where deleted = 0 and createdtime between (now() - interval '6 month') and now() ) as crmentity inner join (select * from activity where activitytype = 'Emails

[PERFORM] query tuning help

Can any one please help me in tuning the query? explain select * from (select * from crmentity where deleted = 0 and createdtime between (now() - interval '6 month') and now() ) as crmentity inner join (select * from activity where activitytype = 'Emails' and date_start between (now() - interval

Re: [PERFORM] Query tuning help

Hi Dan, I tried to understand your query, but I couldn't get my understanding of the query and your description in sync. Why do you use sub selects? Wouldn't a simple "recordtext like '%RED%'" do the trick too? You combine all your where conditions with and. To me this looks like you get only

Re: [PERFORM] Query tuning help

Quoting Russell Smith <[EMAIL PROTECTED]>: > On Mon, 9 May 2005 11:49 am, Dan Harris wrote: > > On May 8, 2005, at 6:51 PM, Russell Smith wrote: > [snip] > > select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat > > FROM em > > JOIN ea ON em.incidentid = ea.incidentid --- sligh

Re: [PERFORM] Query tuning help

In article <[EMAIL PROTECTED]>, Dan Harris <[EMAIL PROTECTED]> writes: > On May 8, 2005, at 8:06 PM, Josh Berkus wrote: >> >>> If I were to use tsearch2 for full-text indexing, would I need to >>> create another table that merges all of my recordtext rows into a >>> single 'text' field type? >>

Re: [PERFORM] Query tuning help

On Sun, 8 May 2005 20:31:38 -0600, Dan Harris <[EMAIL PROTECTED]> wrote: > Duly noted. If this method can search across rows, I'm willing to > accept this overhead for the speed it would add. You could use intersect to search across rows. Using tsearch2 will look up the RED and CORVETTE using t

Re: [PERFORM] Query tuning help

On May 8, 2005, at 8:32 PM, Russell Smith wrote: I have run this, and while it is very fast, I'm concerned it's not doing what I need. How fast is very fast? It took 35 seconds to complete versus ~450 my old way. select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat FROM em JOIN

Re: [PERFORM] Query tuning help

On Mon, 9 May 2005 11:49 am, Dan Harris wrote: > > On May 8, 2005, at 6:51 PM, Russell Smith wrote: > [snip] > > select distinct em.incidentid, ea.recordtext as retdata, eg.long, > > eg.lat > > FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >= > > '2005-1-1 00:00' > > AND

Re: [PERFORM] Query tuning help

On May 8, 2005, at 8:06 PM, Josh Berkus wrote: If I were to use tsearch2 for full-text indexing, would I need to create another table that merges all of my recordtext rows into a single 'text' field type? No. Read the OpenFTS docs, they are fairly clear on how to set up a simple FTS index. (TSe

Re: [PERFORM] Query tuning help

Dan Harris <[EMAIL PROTECTED]> writes: >> -> Index Scan using ea1 on ea (cost=0.00..2736.43 rows=42 width=47) >> (actual time=2.085..2.309 rows=2 loops=473) >> Index Cond: ((ea.incidentid)::text = ("outer".incidentid)::text) >> Filter: (((recordtext)::text ~~ '%RED%'::text) OR ((reco

Re: [PERFORM] Query tuning help

Dan, > While I believe you, I'm confused by this line in my original EXPLAIN > > ANALYZE: > >> -> Index Scan using ea1 on ea (cost=0.00..2736.43 rows=42 width=47) > >> (actual time=2.085..2.309 rows=2 loops=473) > >> Index Cond: > >> ((ea.incidentid)::text

Re: [PERFORM] Query tuning help

On May 8, 2005, at 6:51 PM, Russell Smith wrote: On Mon, 9 May 2005 09:20 am, Dan Harris wrote: You cannot use an index for %CORVETTE%, or %RED%. There is no way for the index to know if a row had that in the middle without scanning the whole index. So it's much cheaper to do a sequence scan.

Re: [PERFORM] Query tuning help

Russell Smith <[EMAIL PROTECTED]> writes: > On Mon, 9 May 2005 09:20 am, Dan Harris wrote: >> and ( recordtext like '%RED%' or recordtext like '%CORVETTE%' ) >> > You cannot use an index for %CORVETTE%, or %RED%. Not a btree index anyway. Dan might have some success here with a full-text-ind

Re: [PERFORM] Query tuning help

Dan, > and (  recordtext like '%RED%' or recordtext like '%CORVETTE%'  )   It is simply not possible to use B-tree indexes on these kind of text queries. B-trees require you to start at the "left" side of the field, because B-trees locate records via <> tests. "Anywhere in the field"

Re: [PERFORM] Query tuning help

On Mon, 9 May 2005 09:20 am, Dan Harris wrote: > Sorry to bother everyone with yet another "my query isn't using an > index" problem but I am over my head on this one.. I am open to ways > of restructuring this query to perform better. > > I have a table, 'ea', with 22 million rows in it. VACU

[PERFORM] Query tuning help

Sorry to bother everyone with yet another "my query isn't using an index" problem but I am over my head on this one.. I am open to ways of restructuring this query to perform better. I have a table, 'ea', with 22 million rows in it. VACUUM ANALYZE has been just run on the table. This is the