Hello
please, send EXPLAIN ANALYZE output instead.
Regards
Pavel Stehule
2011/10/11 CS DBA cs_...@consistentstate.com:
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,
On 11 October 2011 19:52, CS DBA cs_...@consistentstate.com 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,
On 10/11/2011 12:02 PM, Pavel Stehule wrote:
Hello
please, send EXPLAIN ANALYZE output instead.
Regards
Pavel Stehule
2011/10/11 CS DBAcs_...@consistentstate.com:
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
On 10/11/2011 12:03 PM, Szymon Guz wrote:
On 11 October 2011 19:52, CS DBA cs_...@consistentstate.com
mailto:cs_...@consistentstate.com wrote:
Hi all ;
I'm trying to tune a difficult query.
I have 2 tables:
cust_acct (9million rows)
cust_orders (200,000 rows)
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=1)
-
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 =
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
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?
No. Read the
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 --- slight
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. VACUUM
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 text
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-indexing
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.
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 =
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
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.
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 em.entrydate
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
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 the
19 matches
Mail list logo
