Reputation: 2 Apprentice
- Active Members
- Active Posts:
- 65 (0.03 per day)
- 02-December 08
- Profile Views:
- Last Active:
- May 13 2013 06:01 AM
- Dream Kudos:
Posts I've Made
Posted 29 Apr 2013Here are two query plans for the first coalesced subquery. They produce the same set of data
but I'm not completely sure what all of the information here means.
Can you see a reason that the first would not return the aforementioned multiple rows error and the second would?
Nested Loop (cost=1.05..17.16 rows=1 width=516) (actual time=0.156..1.687 rows=206 loops=1) -> Nested Loop (cost=1.05..16.84 rows=1 width=536) (actual time=0.151..1.026 rows=215 loops=1) -> Hash Join (cost=1.05..15.95 rows=3 width=40) (actual time=0.135..0.466 rows=215 loops=1) Hash Cond: (pcfv.customfieldid = pcf.id) -> Seq Scan on prj_customfieldvalue pcfv (cost=0.00..12.45 rows=645 width=60) (actual time=0.011..0.094 rows=645 loops=1) -> Hash (cost=1.04..1.04 rows=1 width=20) (actual time=0.041..0.041 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on prj_customfield pcf (cost=0.00..1.04 rows=1 width=20) (actual time=0.036..0.037 rows=1 loops=1) Filter: ((name)::text = 'InvoicingCountry'::text) -> Index Scan using pk_prj_customfieldoption on prj_customfieldoption pcfo (cost=0.00..0.29 rows=1 width=536) (actual time=0.002..0.002 rows=1 loops=215) Index Cond: (id = pcfv.optionvalue) -> Index Scan using pk_project on project p (cost=0.00..0.30 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=215) Index Cond: (id = pcfv.projectid) Total runtime: 1.904 ms (14 rows)
Seq Scan on "AO_19CA43_PROJECT_EXT" aoext (cost=0.00..10.20 rows=20 width=516) (actual time=0.037..0.107 rows=206 loops=1) Total runtime: 0.189 ms (2 rows)
EDIT: So it was indeed the COALESCEd subqueries that were causing the issue. The first 3 are unnecessary as the information they are trying to join is now all stored in the same table.
Posted 26 Apr 2013Tell me about ti Jack! I've been racking my brain for nearly two days now.
I've tried running the COALESCEd subqueries independently but it doesn't seem possible to do this for some... ie the ones with complicated FROM and/or WHERE clauses.
Essentially though, all that has changed between views is the p.id = pe.id WHERE clauses have been removed as those 2 tables have been merged and now each id is unique so I don't know why this should be a problem
- Member Title:
- D.I.C Head
- Age Unknown
- Birthday Unknown
pistolizedpete hasn't added any friends yet.