db1=# explain analyze SELECT DISTINCT ON (NG.vl1, creat, NG.flati) NG.record, NG.commr1, NG.docst, NG.docin, NG.doc, NG.flato, NG.flati AS flati, NG.creat AS creat, reportxm.id, reportxm.hide, reportxm.read, reportxm.cc, reportxm.pending, reportxm.comment, reportxm.envr1, reportxm.time AS time FROM record_view AS ft, reportxm, record_view AS ft0 WHERE reportxm.regcompany=261333178 AND reportxm.hide=FALSE AND reportxm.envr1=NG.envr1 AND ft0.flati=NG.flati AND ((FALSE) OR ( ft0.vl0 IN (SELECT subst0.id FROM mlist1a AS subst0 WHERE (((subst0.lastname ILIKE '%monthly%')) OR ((subst0.surname ILIKE '%monthly%')) OR ((subst0.company ILIKE '%monthly%')) OR ((subst0.company_short ILIKE '%monthly%')))) AND ft0.vl0 IN (SELECT subst0.id FROM mlist1a AS subst0 WHERE (((subst0.lastname ILIKE '%report%')) OR ((subst0.surname ILIKE '%report%')) OR ((subst0.company ILIKE '%report%')) OR ((subst0.company_short ILIKE '%report%'))))) OR (((ft0.vl2 ILIKE '%monthly%' AND ft0.vl2 ILIKE '%report%')))) AND NG.docst=1 ORDER BY NG.vl1 DESC, creat DESC LIMIT 1000 OFFSET 0; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=133779.07..135568.96 rows=71 width=171) (actual time=80224.317..80224.744 rows=19 loops=1) -> Unique (cost=133779.07..135568.96 rows=71 width=171) (actual time=80224.307..80224.676 rows=19 loops=1) -> Sort (cost=133779.07..134226.54 rows=178989 width=171) (actual time=80224.300..80224.430 rows=91 loops=1) Sort Key: NG.vl1, NG.creat, NG.flati -> Hash Join (cost=4938.63..118162.72 rows=178989 width=171) (actual time=79708.318..80222.796 rows=91 loops=1) Hash Cond: (("outer".envr1)::oid = "inner".envr1) -> Bitmap Heap Scan on reportxm (cost=530.88..110615.56 rows=28411 width=51) (actual time=581.525..1086.185 rows=105001 loops=1) Recheck Cond: (regcompany = 261333178) Filter: (NOT hide) -> Bitmap Index Scan on reportxm_reg_index (cost=0.00..530.88 rows=56822 width=0) (actual time=103.483..103.483 rows=176807 loops=1) Index Cond: (regcompany = 261333178) -> Hash (cost=4404.60..4404.60 rows=1260 width=124) (actual time=78972.309..78972.309 rows=20 loops=1) -> Hash Join (cost=3344.68..4404.60 rows=1260 width=124) (actual time=78765.710..78972.200 rows=20 loops=1) Hash Cond: ("outer".flati = "inner".flati) -> Subquery Scan ft0 (cost=2936.65..3966.22 rows=3550 width=32) (actual time=78211.051..78417.354 rows=20 loops=1) Filter: (((hashed subplan) AND (hashed subplan)) OR ((vl2 ~~* '%monthly%'::text) AND (vl2 ~~* '%report%'::text))) -> Unique (cost=2909.44..3654.99 rows=14201 width=320) (actual time=78196.706..78364.502 rows=29247 loops=1) -> Sort (cost=2909.44..2944.94 rows=14201 width=320) (actual time=78196.698..78239.799 rows=29247 loops=1) Sort Key: record, commr1, envr1, docin, creat, flati, flato, doc, docst, vlord, vl0, vl1, vl2, vl3, vl4, vl5, vl6, vl7, vl8, vl9 -> Append (cost=0.00..1930.02 rows=14201 width=320) (actual time=0.052..396.577 rows=29247 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..872.30 rows=3965 width=320) (actual time=0.047..164.191 rows=16382 loops=1) -> Seq Scan on recordspecA (cost=0.00..832.65 rows=3965 width=320) (actual time=0.030..87.355 rows=16382 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..276.52 rows=2676 width=288) (actual time=0.042..22.825 rows=2256 loops=1) -> Seq Scan on recordspecB (cost=0.00..249.76 rows=2676 width=288) (actual time=0.022..12.342 rows=2256 loops=1) -> Subquery Scan "*SELECT* 3" (cost=0.00..456.32 rows=4416 width=320) (actual time=0.041..62.629 rows=6355 loops=1) -> Seq Scan on recordspecC (cost=0.00..412.16 rows=4416 width=320) (actual time=0.022..33.343 rows=6355 loops=1) -> Subquery Scan "*SELECT* 4" (cost=0.00..324.88 rows=3144 width=288) (actual time=0.037..41.384 rows=4254 loops=1) -> Seq Scan on recordspecC (cost=0.00..293.44 rows=3144 width=288) (actual time=0.020..21.561 rows=4254 loops=1) SubPlan -> Seq Scan on mlist1a subst0 (cost=0.00..13.60 rows=1 width=4) (never executed) Filter: ((lastname ~~* '%report%'::text) OR (surname ~~* '%report%'::text) OR (company ~~* '%report%'::text) OR (company_short ~~* '%report%'::text)) -> Seq Scan on mlist1a subst0 (cost=0.00..13.60 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1) Filter: ((lastname ~~* '%monthly%'::text) OR (surname ~~* '%monthly%'::text) OR (company ~~* '%monthly%'::text) OR (company_short ~~* '%monthly%'::text)) -> Hash (cost=407.85..407.85 rows=71 width=124) (actual time=554.536..554.536 rows=10932 loops=1) -> Subquery Scan ft (cost=403.42..407.85 rows=71 width=124) (actual time=318.749..496.203 rows=10932 loops=1) -> Unique (cost=403.42..407.14 rows=71 width=320) (actual time=318.738..448.646 rows=10932 loops=1) -> Sort (cost=403.42..403.59 rows=71 width=320) (actual time=318.727..339.305 rows=10932 loops=1) Sort Key: record, commr1, envr1, docin, creat, flati, flato, doc, docst, vlord, vl0, vl1, vl2, vl3, vl4, vl5, vl6, vl7, vl8, vl9 -> Append (cost=78.88..401.23 rows=71 width=320) (actual time=5.197..192.868 rows=10932 loops=1) -> Subquery Scan "*SELECT* 1" (cost=78.88..148.98 rows=20 width=320) (actual time=5.192..77.696 rows=5494 loops=1) -> Bitmap Heap Scan on recordspecA (cost=78.88..148.78 rows=20 width=320) (actual time=5.172..51.791 rows=5494 loops=1) Recheck Cond: (docst = 1) -> Bitmap Index Scan on recordspecA_envr1 (cost=0.00..78.88 rows=20 width=0) (actual time=4.787..4.787 rows=5494 loops=1) Index Cond: (docst = 1) -> Subquery Scan "*SELECT* 2" (cost=20.37..62.17 rows=13 width=288) (actual time=1.067..17.539 rows=1223 loops=1) -> Bitmap Heap Scan on recordspecB (cost=20.37..62.04 rows=13 width=288) (actual time=1.049..11.626 rows=1223 loops=1) Recheck Cond: (docst = 1) -> Bitmap Index Scan on recordspecB_envr1 (cost=0.00..20.37 rows=13 width=0) (actual time=0.899..0.899 rows=1223 loops=1) Index Cond: (docst = 1) -> Subquery Scan "*SELECT* 3" (cost=40.46..110.96 rows=22 width=320) (actual time=2.252..35.328 rows=2540 loops=1) -> Bitmap Heap Scan on recordspecC (cost=40.46..110.74 rows=22 width=320) (actual time=2.236..23.051 rows=2540 loops=1) Recheck Cond: (docst = 1) -> Bitmap Index Scan on recordspecC_envr1 (cost=0.00..40.46 rows=22 width=0) (actual time=2.020..2.020 rows=2540 loops=1) Index Cond: (docst = 1) -> Subquery Scan "*SELECT* 4" (cost=28.00..79.13 rows=16 width=288) (actual time=1.496..22.882 rows=1675 loops=1) -> Bitmap Heap Scan on recordspecC (cost=28.00..78.97 rows=16 width=288) (actual time=1.478..14.949 rows=1675 loops=1) Recheck Cond: (docst = 1) -> Bitmap Index Scan on recordspecC_envr1 (cost=0.00..28.00 rows=16 width=0) (actual time=1.336..1.336 rows=1675 loops=1) Index Cond: (docst = 1) Total runtime: 80239.122 ms (60 rows)