Re: [GENERAL] join question
thanks. I shall try it.Also, thanks for putting my name in cvs log ;)
Re: [GENERAL] join question
"=?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 this plan, or anything at all like it, >> from 8.1 ;-)) >> > nope, that's up2date cvs head. I always test stuff on cvs head first, I just committed a patch that might help a bit with that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join question
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. If not, it'd be a good idea to test with more > realistically-sized tables before deciding what's "faster". > I am sure it at least reads some disc, because I can see peak in hd read - up to about 10-20MB/s during that query's execution. -- GJ
Re: [GENERAL] join question
>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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join question
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 rows=1 > > width=8) (actual time=15.195..8216.448 rows=2 loops=1) > > The fact that it's getting a faster plan despite being completely wrong > about the rowcount means that the cost parameters are way off for your > situation. 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. If not, it'd be a good idea to test with more > realistically-sized tables before deciding what's "faster". > tell me about it. even tho I am a rookie here, that cough my attention too. > > 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 this plan, or anything at all like it, > from 8.1 ;-)) > nope, that's up2date cvs head. I always test stuff on cvs head first, only run 8.1 in the office/production/testing - and I already suggested to the powers to be, that we need to move to 8.3 pronto, for several million reasons. Thanks Tom for your opinion :) -- GJ
Re: [GENERAL] join question
"=?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 fact that it's getting a faster plan despite being completely wrong about the rowcount means that the cost parameters are way off for your situation. 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. If not, it'd be a good idea to test with more realistically-sized tables before deciding what's "faster". 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 this plan, or anything at all like it, from 8.1 ;-)) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join question
"we're even more in the dark than you are." :) so here are the plans, that's the real table run. QUERY PLAN after -- Sort (cost=37807.04..37807.05 rows=1 width=50) (actual time=9788.642..9805.832 rows=2 loops=1) Sort Key: s.nodeid Sort Method: external sort Disk: 1320kB -> Nested Loop (cost=376.60..37807.03 rows=1 width=50) (actual time=15.454..9629.198 rows=2 loops=1) -> Nested Loop Anti Join (cost=376.60..37800.27 rows=1 width=50) (actual time=15.347..9077.445 rows=2 loops=1) -> Nested Loop (cost=376.60..37797.99 rows=1 width=50) (actual time=15.308..8927.428 rows=2 loops=1) -> Hash Anti Join (cost=376.60..37791.22 rows=1 width=8) (actual time=15.195..8216.448 rows=2 loops=1) Hash Cond: (e.accountid = account.id) -> Bitmap Heap Scan on efoo e (cost=368.23..37709.63 rows=19523 width=8) (actual time=14.981..8166.262 rows=2 loops=1) Recheck Cond: (packageid = 497) Filter: ((startdate <= now()) AND (enddate > now())) -> Bitmap Index Scan on efoo_packageid_idx (cost=0.00..363.35 rows=19523 width=0) (actual time=9.694..9.694 rows=2 loops=1) Index Cond: (packageid = 497) -> Hash (cost=8.35..8.35 rows=1 width=8) (actual time=0.136..0.136 rows=1 loops=1) -> Index Scan using account_banned_idx on account (cost=0.00..8.35 rows=1 width=8) (actual time=0.129..0.131 rows=1 loops=1) Index Cond: (banned = true) Filter: banned -> Index Scan using bbaccididx on bb s (cost=0.00..6.76 rows=1 width=42) (actual time=0.030..0.032 rows=1 loops=2) Index Cond: (s.accountid = e.accountid) -> Index Scan using bbar_bbid_key on bbar b (cost=0.00..2.27 rows=1 width=11) (actual time=0.005..0.005 rows=0 loops=2) Index Cond: ((b.bbid)::text = (s.id)::text) -> Index Scan using acct_ididx on account a (cost=0.00..6.75 rows=1 width=24) (actual time=0.024..0.025 rows=1 loops=2) Index Cond: (a.id = e.accountid) Total runtime: 9815.280 ms and before: QUERY PLAN before -- Sort (cost=130129.98..130178.78 rows=19521 width=50) (actual time=16156.145..16170.234 rows=2 loops=1) Sort Key: s.nodeid Sort Method: external merge Disk: 1312kB -> Hash Anti Join (cost=78755.00..128101.84 rows=19521 width=50) (actual time=12836.008..16071.668 rows=2 loops=1) Hash Cond: ((s.id)::text = (b.bbid)::text) -> Hash Join (cost=78752.17..127830.60 rows=19523 width=50) (actual time=12825.755..16043.271 rows=2 loops=1) Hash Cond: (e.accountid = s.accountid) -> Merge Join (cost=39100.97..79171.13 rows=19523 width=32) (actual time=11496.544..12614.860 rows=2 loops=1) Merge Cond: (a.id = e.accountid) -> Index Scan using acct_ididx on account a (cost=0.00..37277.39 rows=102 width=24) (actual time=0.183..859.610 rows=50 loops=1) Filter: (banned <> true) -> Sort (cost=39100.93..39149.73 rows=19523 width=8) (actual time=11496.268..11507.031 rows=2 loops=1) Sort Key: e.accountid Sort Method: external sort Disk: 472kB -> Bitmap Heap Scan on efoo e (cost=368.23..37709.63 rows=19523 width=8) (actual time=14.640..11395.226 rows=2 loops=1) Recheck Cond: (packageid = 497) Filter: ((startdate <= now()) AND (enddate > now())) -> Bitmap Index Scan on efoo_packageid_idx (cost=0.00..363.35 rows=19523 width=0) (actual time=9.377..9.377 rows=2 loops=1) Index Cond: (packageid = 497) -> Hash (cost=18850.09..18850.09 rows=109 width=42) (actual time=1326.158..1326.158 rows=109 loops=1) -> Seq Scan on bb s (cost=0.00..18850.09 rows=109 width=42) (actual time=0.032..424.731 rows=109 loops=1) -> Hash (cost=1.81..1.81 rows=81 width=11) (actual time=10.111..10.111 rows=81 loops=1) -> Seq Scan on bbar b (cost=0.00..1.81 rows=81 width=11) (actual t
Re: [GENERAL] join question
"=?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 show us EXPLAIN ANALYZE output, we're even more in the dark than you are. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] join question
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 > now(); 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: select a.*, b.* from foo a join bar b on a.id = b.a_id where not exists ( select id from foo where foo.id = b.a_id and foo.banned <> true ) and a.start <= now() and b.end > now(); plans differ, obviously - second one uses index to lookup .banned in foo, whilst first one goes for seq scan. result is the same, but I was actually expecting quite opposite. So is join on 1-2M rows a bad idea ? The effect can be seen on both 8.1 and cvs head. I would be grateful for someone clarifying that to me. -- GJ
Re: [GENERAL] Join Question
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 join to left outer join. Is this correct? Types of JOIN: * [ INNER ] JOIN * LEFT [ OUTER ] JOIN * RIGHT [ OUTER ] JOIN * FULL [ OUTER ] JOIN * CROSS JOIN As usual, "[ .. ]" means that that word can be omitted. "left join == inner join" is absolutely incorrect, I'm afraid you need to refresh you memory and read the manual (http://www.postgresql.org/docs/8.1/interactive/sql-select.html, find "join_type"). This part of Postgres conforms to standard, all major DBMSs follow this semantics too. -- Best regards, Nikolay ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Join Question
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 left join to left outer join. Is this > correct? A left or right join IS an outer join, as is a full join. the outer is just syntactic sugar. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Join Question
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
Re: [GENERAL] Join question
about 5% of b are null.. any idea on how to word a query like what I'm looking for? I have no idea in SQL how to use the results of one select in another (I usually just do all of it in perl). Travis -Original Message- From: Jeffrey Melloy [mailto:[EMAIL PROTECTED] Sent: Friday, August 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.. then in perl I can check if b is not null and if is > isn't then I do a second query? > > Travis > I would say it depends on the table distribution (% of b that are null, etc). At that point, you're just going to have to experiment, I think, and see what works best for you. My gut says that even including a subselect, it will be faster than checking in perl and issuing another query. But I have no idea, and the proper solution might change drastically based on your data. Jeff ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Join question
btw.. thanks for all the help. Travis -Original Message- From: Jeffrey Melloy [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 12:32 AM To: Williams, Travis L, NEO Cc: Thomas A. Lowery; [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 between table1 & table2 (unless you can join on likes >> You can do something like this, but I can't promise any great performance: select case when b is null then a else (select d from table2 where d ~* b) end as value fromtable1; jmelloy=# select * from table1; a | b ---+-- 1 | 2 | 3 | 4 | for 5 | asdf 6 | coo (6 rows) jmelloy=# select * from table2; d -- forsythe manasdf cool (3 rows) jmelloy=# select case when b is null then a::varchar else jmelloy-# (select d from table2 where d ~* b) end as value jmelloy-# from table1; value -- 1 2 3 forsythe manasdf cool (6 rows) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Join question
Performace wise would I be better off just doing 2 query's.. i.e. select a,b from table1.. then in perl I can check if b is not null and if is isn't then I do a second query? Travis -Original Message- From: Jeffrey Melloy [mailto:[EMAIL PROTECTED] Sent: Friday, August 29, 2003 12:32 AM To: Williams, Travis L, NEO Cc: Thomas A. Lowery; [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 between table1 & table2 (unless you can join on likes >> You can do something like this, but I can't promise any great performance: select case when b is null then a else (select d from table2 where d ~* b) end as value fromtable1; jmelloy=# select * from table1; a | b ---+-- 1 | 2 | 3 | 4 | for 5 | asdf 6 | coo (6 rows) jmelloy=# select * from table2; d -- forsythe manasdf cool (3 rows) jmelloy=# select case when b is null then a::varchar else jmelloy-# (select d from table2 where d ~* b) end as value jmelloy-# from table1; value -- 1 2 3 forsythe manasdf cool (6 rows) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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 between table1 & table2 (unless you can join on likes You can do something like this, but I can't promise any great performance: select case when b is null then a else (select d from table2 where d ~* b) end as value fromtable1; jmelloy=# select * from table1; a | b ---+-- 1 | 2 | 3 | 4 | for 5 | asdf 6 | coo (6 rows) jmelloy=# select * from table2; d -- forsythe manasdf cool (3 rows) jmelloy=# select case when b is null then a::varchar else jmelloy-# (select d from table2 where d ~* b) end as value jmelloy-# from table1; value -- 1 2 3 forsythe manasdf cool (6 rows) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Join question
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 Thu, Aug 28, 2003 at 09:46:34PM -0400, Williams, Travis L, NEO wrote: > That would probably work great.. except I screwed my question up.. Let > me try again and this time think before I write > > 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 between table1 & table2 (unless you can join on likes ;) > ). So the second select uses the value of table1 column b in the like condition? > -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 > > > 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 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 join on it and table (2) col c to get the contents > > of table (2) col d.. I can do this in multiple queries.. but was > > wondering if I could do it in 1. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Join question
On Thu, Aug 28, 2003 at 09:46:34PM -0400, Williams, Travis L, NEO wrote: > That would probably work great.. except I screwed my question up.. Let > me try again and this time think before I write > > 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 between table1 & table2 (unless you can join on likes ;) > ). So the second select uses the value of table1 column b in the like condition? > -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 > > > 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 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 join on it and table (2) col c to get the contents > > of table (2) col d.. I can do this in multiple queries.. but was > > wondering if I could do it in 1. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Join question
That would probably work great.. except I screwed my question up.. Let me try again and this time think before I write 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 between table1 & table2 (unless you can join on likes ;) ). but thanks for showing me unions.. (I just learned about joins).. more knowledge to help me on my way! 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 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 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 join on it and table (2) col c to get the contents > of table (2) col d.. I can do this in multiple queries.. but was > wondering if I could do it in 1. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Join question
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 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 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 join on it and table (2) col c to get the contents > of table (2) col d.. I can do this in multiple queries.. but was > wondering if I could do it in 1. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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 -0400, Williams, Travis L, NEO wrote: > Question, > > I have a table (1) 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 join on it and table (2) col c to get the contents > of table (2) col d.. I can do this in multiple queries.. but was > wondering if I could do it in 1. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
