Public feed
#601270
EXPLAIN ANALYZE
SELECT a.a_id,att.att_value FROM assets a,attributes att WHERE
a.a_id = att.att_a_id AND
att.att_s_id = 3
AND
a.a_id IN(
SELECT sa.att_a_id FROM searchable_attributes sa
WHERE
sa.grc_gr_id = 2 AND
sa.att_s_id = 3 AND (sa.att_value != '12157_3' AND sa.att_value LIKE '%20%')
INTERSECT
SELECT sa.att_a_id FROM searchable_attributes sa
WHERE
sa.grc_gr_id = 2 AND
sa.att_s_id = 11 AND sa.att_value != 300
)
ORDER BY att_value;
QUERY PLAN
Sort (cost=587418.97..587460.71 rows=16698 width=26) (actual time=2806.311..2806.413 rows=266 loops=1)
Sort Key: att.att_value
-> Hash Join (cost=567246.76..586247.82 rows=16698 width=26) (actual time=2291.044..2805.147 rows=266 loops=1)
" Hash Cond: (""outer"".att_a_id = ""inner"".a_id)"
-> Seq Scan on attributes att (cost=0.00..17575.61 rows=56441 width=26) (actual time=2.934..475.230 rows=57705 loops=1)
Filter: (att_s_id = 3)
-> Hash (cost=567126.90..567126.90 rows=15146 width=16) (actual time=2278.486..2278.486 rows=0 loops=1)
-> Merge IN Join (cost=565322.78..567126.90 rows=15146 width=16) (actual time=2184.406..2278.123 rows=266 loops=1)
" Merge Cond: (""outer"".a_id = ""inner"".att_a_id)"
-> Index Scan using assets_pkey on assets a (cost=0.00..1319.07 rows=57707 width=8) (actual time=0.062..54.708 rows=57698 loops=1)
-> Materialize (cost=565322.78..565474.24 rows=15146 width=8) (actual time=2181.276..2192.553 rows=266 loops=1)
" -> Subquery Scan ""IN_subquery"" (cost=564398.91..565307.63 rows=15146 width=8) (actual time=2181.271..2192.306 rows=266 loops=1)"
-> SetOp Intersect (cost=564398.91..565156.18 rows=15145 width=8) (actual time=2181.268..2192.015 rows=266 loops=1)
-> Sort (cost=564398.91..564777.54 rows=151454 width=8) (actual time=2181.089..2185.758 rows=11716 loops=1)
Sort Key: att_a_id
-> Append (cost=22185.20..549592.30 rows=151454 width=8) (actual time=475.026..2169.035 rows=11716 loops=1)
" -> Subquery Scan ""*SELECT* 1"" (cost=22185.20..124495.47 rows=24480 width=8) (actual time=475.025..849.538 rows=3747 loops=1)"
-> Hash Join (cost=22185.20..124250.67 rows=24480 width=8) (actual time=475.021..844.532 rows=3747 loops=1)
" Hash Cond: (""outer"".a_object_class = ""inner"".grc_oc_id)"
-> Nested Loop (cost=22183.53..124012.04 rows=6473 width=16) (actual time=474.957..838.332 rows=3747 loops=1)
-> Nested Loop (cost=0.00..12.90 rows=1 width=8) (actual time=0.248..0.362 rows=1 loops=1)
-> Seq Scan on rights (cost=0.00..2.37 rows=2 width=8) (actual time=0.031..0.117 rows=1 loops=1)
Filter: (((r_type)::text = '1'::text) AND (3 = r_original_id))
-> Index Scan using grouprights_idx on grouprights (cost=0.00..5.26 rows=1 width=8) (actual time=0.213..0.240 rows=1 loops=1)
" Index Cond: ((grouprights.gr_value >= 2) AND (grouprights.gr_rights_id = ""outer"".r_id) AND (2 = grouprights.gr_group_id))"
-> Merge Join (cost=22183.53..123897.59 rows=10155 width=16) (actual time=474.703..832.942 rows=3747 loops=1)
" Merge Cond: (""outer"".a_id = ""inner"".att_a_id)"
" Join Filter: ((subplan) OR (""outer"".a_fieldset IS NULL) OR (subplan))"
-> Index Scan using assets_pkey on assets (cost=0.00..1319.07 rows=57707 width=20) (actual time=0.004..58.228 rows=57704 loops=1)
-> Sort (cost=22183.53..22211.75 rows=11286 width=12) (actual time=474.516..476.193 rows=3747 loops=1)
Sort Key: attributes.att_a_id
-> Seq Scan on attributes (cost=0.00..21423.86 rows=11286 width=12) (actual time=2.998..471.362 rows=3747 loops=1)
Filter: ((att_s_id = 3) AND (att_value <> '12157_3'::text) AND (att_value ~~ '%20%'::text))
SubPlan
-> Index Scan using fieldsets_fields_idx on fieldsets_fields (cost=0.00..5.75 rows=1 width=8) (never executed)
Index Cond: ((fsf_s_id = $1) AND (fsf_fs_id = $2))
-> Seq Scan on object_type_fields (cost=0.00..3.12 rows=1 width=8) (actual time=0.067..0.067 rows=1 loops=3747)
Filter: ((otf_id = $0) AND (otf_s_id = $1))
-> Hash (cost=1.66..1.66 rows=4 width=8) (actual time=0.053..0.053 rows=0 loops=1)
-> Seq Scan on grouprights_class grc (cost=0.00..1.66 rows=4 width=8) (actual time=0.020..0.050 rows=3 loops=1)
Filter: ((grc_value = 1) AND (grc_gr_id = 2))
" -> Subquery Scan ""*SELECT* 2"" (cost=3494.75..425096.83 rows=126974 width=8) (actual time=384.477..1310.876 rows=7969 loops=1)"
-> Hash Join (cost=3494.75..423827.09 rows=126974 width=8) (actual time=384.472..1301.081 rows=7969 loops=1)
" Hash Cond: (""outer"".att_a_id = ""inner"".a_id)"
" Join Filter: ((subplan) OR (""inner"".a_fieldset IS NULL) OR (subplan))"
-> Seq Scan on attributes (cost=0.00..19499.74 rows=58539 width=12) (actual time=195.530..491.771 rows=7969 loops=1)
Filter: ((att_s_id = 11) AND (att_value <> '300'::text))
-> Hash (cost=3123.91..3123.91 rows=44334 width=20) (actual time=183.875..183.875 rows=0 loops=1)
-> Hash Join (cost=9.34..3123.91 rows=44334 width=20) (actual time=0.380..142.354 rows=57705 loops=1)
" Hash Cond: (""outer"".a_object_class = ""inner"".grc_oc_id)"
-> Seq Scan on assets (cost=0.00..1002.07 rows=57707 width=20) (actual time=0.002..44.398 rows=57705 loops=1)
-> Hash (cost=9.33..9.33 rows=4 width=8) (actual time=0.357..0.357 rows=0 loops=1)
-> Nested Loop (cost=0.00..9.33 rows=4 width=8) (actual time=0.127..0.350 rows=3 loops=1)
-> Nested Loop (cost=0.00..7.63 rows=1 width=8) (actual time=0.102..0.292 rows=1 loops=1)
-> Seq Scan on rights (cost=0.00..2.37 rows=1 width=8) (actual time=0.030..0.097 rows=1 loops=1)
Filter: (((r_type)::text = '1'::text) AND (11 = r_original_id))
-> Index Scan using grouprights_idx on grouprights (cost=0.00..5.26 rows=1 width=8) (actual time=0.061..0.182 rows=1 loops=1)
" Index Cond: ((grouprights.gr_value >= 2) AND (grouprights.gr_rights_id = ""outer"".r_id) AND (2 = grouprights.gr_group_id))"
-> Seq Scan on grouprights_class grc (cost=0.00..1.66 rows=4 width=8) (actual time=0.019..0.047 rows=3 loops=1)
Filter: ((grc_value = 1) AND (grc_gr_id = 2))
SubPlan
-> Index Scan using fieldsets_fields_idx on fieldsets_fields (cost=0.00..5.75 rows=1 width=8) (never executed)
Index Cond: ((fsf_s_id = $1) AND (fsf_fs_id = $2))
-> Seq Scan on object_type_fields (cost=0.00..3.12 rows=1 width=8) (actual time=0.069..0.069 rows=1 loops=7969)
Filter: ((otf_id = $0) AND (otf_s_id = $1))
Total runtime: 2807.493 ms