Hey folks,
I am trying to rewrite a query here, that takes 1.5m atm to finish. I got it
down to 20s, and still trying to pin it down.
basically, a query looks something like that atm:
select a.*, b.*
from a
join b on a.id = b.a_id and a.banned true
where
a.start = now()
and
b.end
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= [EMAIL PROTECTED] writes:
that's 20s query, and now I got it down to 10s , by using something - which
in my eyes would be always wrong - and against all logic. So if someone
could please explain to me why is it faster:
[ shrug... ] If you aren't going to
we're even more in the dark than you are.
:)
so here are the plans, that's the real table run.
QUERY PLAN after
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= [EMAIL PROTECTED] writes:
so here are the plans, that's the real table run.
Hmm, well this rowcount estimate is way off:
- Hash Anti Join (cost=376.60..37791.22 rows=1
width=8) (actual time=15.195..8216.448 rows=2 loops=1)
The
On Thu, Oct 23, 2008 at 12:25 AM, Tom Lane [EMAIL PROTECTED] wrote:
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= [EMAIL PROTECTED] writes:
so here are the plans, that's the real table run.
Hmm, well this rowcount estimate is way off:
- Hash Anti Join (cost=376.60..37791.22
Sort Method: external sort Disk: 1320kB
One simple speedup could be upping Your work_mem to 2M for this query,
so the sorts are in memory.
btw: Last time I used Postgres, it did not show the sort method. Cool.
Greetings
Marcin Mank
--
Sent via pgsql-general mailing list
On Thu, Oct 23, 2008 at 12:25 AM, Tom Lane [EMAIL PROTECTED] wrote:
It looks like you are testing a case where the tables all
fit in memory. Do you expect that to be the reality for your production
use? If so, you might want to reduce random_page_cost to something
close to 1 to reflect it.
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= [EMAIL PROTECTED] writes:
On Thu, Oct 23, 2008 at 12:25 AM, Tom Lane [EMAIL PROTECTED] wrote:
I'm not sure why the rowcount estimate is so far off, but the antijoin
code is all new and probably there's an estimation bug in there
somewhere. (You didn't get
thanks. I shall try it.Also, thanks for putting my name in cvs log ;)
Question,What is the difference between left join, and left outer join?I know the difference between inner and outer joins, but I was thinking that left join == inner join. But from what I am now seeing, it appears that PG is equating left join to left outer join. Is this correct?
Thanks,Chris
On Wed, 2006-08-02 at 14:32, Chris Hoover wrote:
Question,
What is the difference between left join, and left outer join?
I know the difference between inner and outer joins, but I was
thinking that left join == inner join. But from what I am now seeing,
it appears that PG is equating
On 8/2/06, Chris Hoover [EMAIL PROTECTED] wrote:
Question,
What is the difference between left join, and left outer join?
I know the difference between inner and outer joins, but I was thinking that
left join == inner join. But from what I am now seeing, it appears that PG
is equating left
Does using a union count as one query?
select a from tst_1 where b is null
union
select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c)
where t1.b is NOT null
On Thu, Aug 28, 2003 at 05:55:27PM -0400, Williams, Travis L, NEO wrote:
Question,
I have a table (1) with 2 col (a b) where b can
Sure.. if it works.. I'm just trying to not have to make multiple calls
to the DB.. I'll try it out..
Thanks,
Travis
-Original Message-
From: Thomas A. Lowery [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 28, 2003 8:06 PM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Join question
PROTECTED]
Sent: Thursday, August 28, 2003 8:06 PM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Join question
Does using a union count as one query?
select a from tst_1 where b is null
union
select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c)
where t1.b is NOT null
On Thu, Aug 28, 2003 at 05:55:27PM
: Thomas A. Lowery [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 28, 2003 8:06 PM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Join question
Does using a union count as one query?
select a from tst_1 where b is null
union
select d from tst_2 t2 join tst_1 t1 on (t1.b = t2.c)
where t1
Yeah.. so that's why I didn't know if I could do it all in the same
statement.
Travis
-Original Message-
From: Thomas A. Lowery [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 28, 2003 8:54 PM
To: Williams, Travis L, NEO
Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Join question
On Thursday, August 28, 2003, at 09:03 PM, Williams, Travis L, NEO
wrote:
I have a table1 with 2 col (a b) where b can sometimes be null. I
need a query that if B is null I get back the contents of A.. but if B
is not null I do a select d from table2 where d like '%b%' There is
nothing to join
29, 2003 12:44 AM
To: Williams, Travis L, NEO
Cc: Thomas A. Lowery; [EMAIL PROTECTED]
Subject: Re: [GENERAL] Join question
On Friday, August 29, 2003, at 12:36 AM, Williams, Travis L, NEO wrote:
Performace wise would I be better off just doing 2 query's.. i.e.
select
a,b from table1
19 matches
Mail list logo
