agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedWHERE ... NOT NULL ... OR ... (SELECT...)
3+ messages / 2 participants
[nested] [flat]
* WHERE ... NOT NULL ... OR ... (SELECT...)
@ 2015-08-23 21:01 Mario Splivalo <mario@splivalo.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Mario Splivalo @ 2015-08-23 21:01 UTC (permalink / raw)
To: pgsql-sql
I have a query, like this:
valipile=# explain select * from account_analytic_line where move_id in
(SELECT id FROM account_move_line);
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Semi Join (cost=60799.74..96694.82 rows=329568 width=162)
Hash Cond: (account_analytic_line.move_id = account_move_line.id)
-> Seq Scan on account_analytic_line (cost=0.00..9620.68
rows=329568 width=162)
-> Hash (cost=41292.66..41292.66 rows=1188966 width=4)
-> Seq Scan on account_move_line (cost=0.00..41292.66
rows=1188966 width=4)
(5 rows)
Which is all fine. However, as move_id in account_analytic_line is
NULLable I want to include that one into my query. But then:
valipile=# explain select * from account_analytic_line where move_id is
null or move_id in (SELECT id FROM account_move_line);
QUERY PLAN
-----------------------------------------------------------------------------------------
Seq Scan on account_analytic_line (cost=0.00..9039221110.12
rows=164784 width=162)
Filter: ((move_id IS NULL) OR (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..51882.49 rows=1188966 width=4)
-> Seq Scan on account_move_line (cost=0.00..41292.66
rows=1188966 width=4)
(5 rows)
This, of course, takes forever.
(There are no indexes/constraints/whatever on the tables as I'm deleting
old data from the database)
Now, I did 'circumvent' the waiting with using UNION:
valipile=# explain select * from account_analytic_line where move_id in
(select id from account_move_line) union select * from
account_analytic_line where move_id is null;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=193891.55..212017.84 rows=329569 width=162)
-> Sort (cost=193891.55..194715.47 rows=329569 width=162)
Sort Key: account_analytic_line.id,
account_analytic_line.create_uid, account_analytic_line.create_date,
account_analytic_line.write_date, account_analytic_line.write_uid,
account_analytic_line.amount, account_analytic_line.user_id, account_analy
-> Append (cost=60799.74..109611.18 rows=329569 width=162)
-> Hash Semi Join (cost=60799.74..96694.82 rows=329568
width=162)
Hash Cond: (account_analytic_line.move_id =
account_move_line.id)
-> Seq Scan on account_analytic_line
(cost=0.00..9620.68 rows=329568 width=162)
-> Hash (cost=41292.66..41292.66 rows=1188966
width=4)
-> Seq Scan on account_move_line
(cost=0.00..41292.66 rows=1188966 width=4)
-> Seq Scan on account_analytic_line
account_analytic_line_1 (cost=0.00..9620.68 rows=1 width=162)
Filter: (move_id IS NULL)
(11 rows)
but I'm curious why postgres chooses such poor query plan for the 'OR
column IS NULL' addition ?
Mario
--
Mario Splivalo
mario@splivalo.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: WHERE ... NOT NULL ... OR ... (SELECT...)
@ 2015-08-24 11:34 Dickson S. Guedes <listas@guedesoft.net>
parent: Mario Splivalo <mario@splivalo.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Dickson S. Guedes @ 2015-08-24 11:34 UTC (permalink / raw)
To: Mario Splivalo <mario@splivalo.hr>; +Cc: pgsql-sql
2015-08-23 18:01 GMT-03:00 Mario Splivalo <mario@splivalo.hr>:
[...]
> Now, I did 'circumvent' the waiting with using UNION:
>
> valipile=# explain select * from account_analytic_line where move_id in
> (select id from account_move_line) union select * from
> account_analytic_line where move_id is null;
[...]
> Unique (cost=193891.55..212017.84 rows=329569 width=162)
> -> Sort (cost=193891.55..194715.47 rows=329569 width=162)
I'm not answering your original question, but since the queries in your UNION
will be mutually exclusive, you could replace your UNION by UNION ALL to rip off
the Sort+Unique Node.
[]s
--
Dickson S. Guedes
mail/xmpp: guedes@guedesoft.net - skype: guediz
http://github.com/guedes - http://guedesoft.net
http://www.postgresql.org.br
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: WHERE ... NOT NULL ... OR ... (SELECT...)
@ 2015-08-24 12:13 Mario Splivalo <mario@splivalo.hr>
parent: Dickson S. Guedes <listas@guedesoft.net>
0 siblings, 0 replies; 3+ messages in thread
From: Mario Splivalo @ 2015-08-24 12:13 UTC (permalink / raw)
To: Dickson S. Guedes <listas@guedesoft.net>; +Cc: pgsql-sql
On 08/24/2015 01:34 PM, Dickson S. Guedes wrote:
> 2015-08-23 18:01 GMT-03:00 Mario Splivalo <mario@splivalo.hr>:
>> Unique (cost=193891.55..212017.84 rows=329569 width=162)
>> -> Sort (cost=193891.55..194715.47 rows=329569 width=162)
>
> I'm not answering your original question, but since the queries in your UNION
> will be mutually exclusive, you could replace your UNION by UNION ALL to rip off
> the Sort+Unique Node.
>
Good point, thank you! :)
Mario
--
Mario Splivalo
mario@splivalo.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-08-24 12:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-23 21:01 WHERE ... NOT NULL ... OR ... (SELECT...) Mario Splivalo <mario@splivalo.hr>
2015-08-24 11:34 ` Dickson S. Guedes <listas@guedesoft.net>
2015-08-24 12:13 ` Mario Splivalo <mario@splivalo.hr>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox