ProjectSet
Hi,
I've just been running explain analyze on a slow query and came across
something new to me - a node type of ProjectSet in the plan. What is that?
The plan is here: https://explain.depesz.com/s/4rqA
The query does a cross join of two tables to get every row in one combined
with every row in the other, then runs the similarity function to compare
text in them. The full query is below, apologies for the different
obfuscation to the plan, if that's an issue let me know.
Is there an explanation of ProjectSet anywhere? I can't see one with a
quick google or search of these archives.
Cheers
Oliver
---
SELECT table1.id,
similarity(table2.field1::text, regexp_matches(table1.field3::text,
'product.ame:s*([^-]*)'::text, 'g'::text)::character
varying(100000)::text)::double precision AS similarityscore,
table1.ourid_g3a11eruac8ct55b,
regexp_matches(table1.field3::text, 'product.ame:s*([^-]*)'::text,
'g'::text)::character varying(100000) AS products,
table2.field1,
table2.field2,
table2.abaccount
FROM table1,
table2
WHERE lower(table2.statusofingredient::text) < '6'::text AND
lower(table2.statusofproduct::text) < '5'::text AND table1.fsacreated >=
(date_trunc('day'::text, now()) - '30 days'::interval) AND
lower(table2.bought::text) = 'bought'::text
ORDER BY table1.id DESC NULLS LAST;
On 2 August 2018 at 21:17, Oliver Kohll <oliver@agilechilli.com> wrote:
Is there an explanation of ProjectSet anywhere?
Plan node types and what they each do are not very well documented
outside of the source code.
ProjectSet appears when the SELECT or ORDER BY clause of the query.
They basically just execute the set-returning function(s) for each
tuple until none of the functions return any more records.
Simple Example:
EXPLAIN SELECT generate_series(1,2);
QUERY PLAN
-------------------------------------------------
ProjectSet (cost=0.00..5.02 rows=1000 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
(2 rows)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Ah thanks David, at least I know what it is now. I don't think I have any
set returning functions though will double check in case there's a joined
view that has one. Perhaps it could be the something to do with cross
product which similarly creates multiple rows on the right for each row on
the left side.
In any case, after deleting some obsolete rows, the plan seems to have
changed as the query now executes in a fraction of a second as opposed to
25 seconds.
Oliver
On 2 August 2018 at 13:21:32, David Rowley (david.rowley@2ndquadrant.com)
wrote:
On 2 August 2018 at 21:17, Oliver Kohll <oliver@agilechilli.com> wrote:
Is there an explanation of ProjectSet anywhere?
Plan node types and what they each do are not very well documented
outside of the source code.
ProjectSet appears when the SELECT or ORDER BY clause of the query.
They basically just execute the set-returning function(s) for each
tuple until none of the functions return any more records.
Simple Example:
EXPLAIN SELECT generate_series(1,2);
QUERY PLAN
-------------------------------------------------
ProjectSet (cost=0.00..5.02 rows=1000 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
(2 rows)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 3 August 2018 at 01:16, Oliver Kohll <oliver@agilechilli.com> wrote:
Ah thanks David, at least I know what it is now. I don't think I have any
set returning functions though will double check in case there's a joined
view that has one. Perhaps it could be the something to do with cross
product which similarly creates multiple rows on the right for each row on
the left side.
postgres=# select proretset from pg_proc where proname = 'regexp_matches';
proretset
-----------
t
t
(2 rows)
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services