API tools faq
paste
Guest User

Untitled

a guest
Jan 20th, 2019
100
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.26 KB | None | 0 0
raw download clone embed print report
  1. SELECT DISTINCT title
  2. FROM ja_jobs
  3. WHERE title ILIKE '%RYAN WER%'
  4. AND clientid = 31239
  5. AND time_job > 1457826264
  6. ORDER BY title
  7. LIMIT 10;
  8.  
  9. Limit (cost=5946.40..5946.41 rows=1 width=19) (actual time=2746.759..2746.772 rows=1 loops=1)
  10. -> Unique (cost=5946.40..5946.41 rows=1 width=19) (actual time=2746.753..2746.763 rows=1 loops=1)
  11. -> Sort (cost=5946.40..5946.41 rows=1 width=19) (actual time=2746.750..2746.754 rows=4 loops=1)
  12. Sort Key: "title"
  13. Sort Method: quicksort Memory: 25kB
  14. -> Bitmap Heap Scan on "ja_jobs" (cost=49.02..5946.39 rows=1 width=19) (actual time=576.275..2746.609 rows=4 loops=1)
  15. Recheck Cond: (("clientid" = 31239) AND ("time_job" > 1457826264))
  16. Filter: (("title")::"text" ~~* '%RYAN WER%'::"text")
  17. Rows Removed by Filter: 791
  18. -> Bitmap Index Scan on "ix_jobs_client_times" (cost=0.00..49.02 rows=1546 width=0) (actual time=100.870..100.870 rows=795 loops=1)
  19. Index Cond: (("clientid" = 31239) AND ("time_job" > 1457826264))
  20. Total runtime: 2746.879 ms
  21.  
  22. CREATE INDEX ix_ja_jobs_trgm_gin ON public.ja_jobs USING gin (title gin_trgm_ops);
  23.  
  24. Limit (cost=389.91..389.91 rows=1 width=20) (actual time=3720.511..3720.511 rows=0 loops=1)
  25. -> Unique (cost=389.91..389.91 rows=1 width=20) (actual time=3720.507..3720.507 rows=0 loops=1)
  26. -> Sort (cost=389.91..389.91 rows=1 width=20) (actual time=3720.505..3720.505 rows=0 loops=1)
  27. Sort Key: "title"
  28. Sort Method: quicksort Memory: 25kB
  29. -> Bitmap Heap Scan on "ja_jobs" (cost=385.88..389.90 rows=1 width=20) (actual time=3720.497..3720.497 rows=0 loops=1)
  30. Recheck Cond: (("clientid" = 31239) AND ("time_job" > 1457826264) AND (("title")::"text" ~~ '%RYAN WER%'::"text"))
  31. Rows Removed by Index Recheck: 4
  32. -> BitmapAnd (cost=385.88..385.88 rows=1 width=0) (actual time=3720.469..3720.469 rows=0 loops=1)
  33. -> Bitmap Index Scan on "ix_jobs_client_times" (cost=0.00..50.00 rows=1644 width=0) (actual time=0.142..0.142 rows=795 loops=1)
  34. Index Cond: (("clientid" = 31239) AND ("time_job" > 1457826264))
  35. -> Bitmap Index Scan on "ix_ja_jobs_trgm_gin" (cost=0.00..335.63 rows=484 width=0) (actual time=3720.213..3720.213 rows=32 loops=1)
  36. Index Cond: (("title")::"text" ~~ '%RYAN WER%'::"text")
  37. Total runtime: 3720.653 ms
  38.  
  39. CREATE TABLE public.ja_jobs (
  40. id bigint NOT NULL DEFAULT "nextval"('"ja_jobs_id_seq"'::"regclass"),
  41. refnum character varying(100) NOT NULL DEFAULT ''::character varying,
  42. clientid bigint NOT NULL DEFAULT 0,
  43. customerid bigint,
  44. time_job bigint,
  45. priority smallint NOT NULL DEFAULT 0,
  46. status character varying(255) NOT NULL DEFAULT 'active'::"bpchar",
  47. title character varying(100) NOT NULL DEFAULT ''::character varying,
  48.  
  49. -- some other irrelevant columns
  50. )
  51.  
  52. Indexes:
  53. "ja_jobs_pkey" PRIMARY KEY, "btree" ("id")
  54. "ix_bill_customer_jobs" "btree" ("customerid", "bill_customer")
  55. "ix_clientid_jobs" "btree" ("clientid")
  56. "ix_customerid_job" "btree" ("customerid")
  57. "ix_ja_jobs_clientid_modified_date_created_date" "btree" ("clientid", "modified_date", "created_date")
  58. "ix_ja_jobs_gsdi_pk" "btree" (("id"::"text"))
  59. "ix_ja_jobs_trgm_gin" "gin" ("title" "gin_trgm_ops")
  60. "ix_job_customer_recent_jobs_lookaside_bill_customer" "btree" ("bill_customer", "modified_date")
  61. "ix_job_customer_recent_jobs_lookaside_clientid" "btree" ("clientid", "modified_date")
  62. "ix_job_customer_recent_jobs_lookaside_customer" "btree" ("customerid", "modified_date")
  63. "ix_jobs_charges_and_parts_sort" "btree" (("charges_count" + "parts_count"))
  64. "ix_jobs_client_times" "btree" ("clientid", "time_job", "time_arrival")
  65. "ix_jobs_fts_description_en" "gin" ("full_text_universal_cast"("description"))
  66. "ix_jobs_fts_full_address_en" "gin" ((((("full_text_universal_cast"("address"::"text") || "full_text_universal_cast"("suburb"::"text")) || "full_text_universal_cast"("city"::"text")) || "full_text_universal_cast"("stpr"::"text")) || "full_text_universal_cast"("postc
  67. ode"::"text")))
  68. "ix_jobs_fts_job_number_en" "gin" ("full_text_universal_cast"("job_number"::"text"))
  69. "ix_jobs_fts_refnum_en" "gin" ("full_text_universal_cast"("refnum"::"text"))
  70. "ix_jobs_fts_title_en" "gin" ("full_text_universal_cast"("title"::"text"))
  71. "ix_jobs_full_address_street_first" "btree" (((((COALESCE("address"::character varying, ''::character varying)::"text" || COALESCE(' '::"text" || "suburb"::"text", ''::"text")) || COALESCE(' '::"text" || "city"::"text", ''::"text")) || COALESCE(' '::"text" || "postc
  72. ode"::"text", ''::"text")) || COALESCE(' '::"text" || "stpr"::"text", ''::"text")))
  73. "ix_jobs_paying_customers" "btree" ((COALESCE("bill_customer", "customerid")))
  74. "ix_jobs_status_label_ids" "btree" ("status_label_id")
  75. "ix_jobs_top_by_client" "btree" ("id", "clientid")
  76. "ix_mobiuser_jobs" "btree" ("accepted_mobile_user")
  77. "ix_recurrenceid_jobs" "btree" ("recurrenceid")
  78. "ix_timejob_jobs" "btree" ("time_job")
  79. "ja_jobs_client_type" "btree" ("clientid", "jobtype")
  80. "ja_jobs_the_geom_idx" "gist" ("the_geom")
  81.  
  82. Limit (cost=199669.37..199669.39 rows=10 width=20) (actual time=31523.690..31523.691 rows=1 loops=1)
  83. Buffers: shared hit=26947 read=101574 dirtied=438
  84. -> Sort (cost=199669.37..199669.40 rows=11 width=20) (actual time=31523.686..31523.686 rows=1 loops=1)
  85. Sort Key: "title"
  86. Sort Method: quicksort Memory: 25kB
  87. Buffers: shared hit=26947 read=101574 dirtied=438
  88. -> Bitmap Heap Scan on "ja_jobs" (cost=4850.60..199669.18 rows=11 width=20) (actual time=11714.504..31523.640 rows=1 loops=1)
  89. Recheck Cond: (("clientid" = 2565) AND ("time_job" > 1382496599))
  90. Filter: (("title")::"text" ~~* '%Hislop%'::"text")
  91. Rows Removed by Filter: 207654
  92. Buffers: shared hit=26942 read=101574 dirtied=438
  93. -> Bitmap Index Scan on "ix_jobs_client_times" (cost=0.00..4850.60 rows=155054 width=0) (actual time=11670.956..11670.956 rows=215142 loops=1)
  94. Index Cond: (("clientid" = 2565) AND ("time_job" > 1382496599))
  95. Buffers: shared hit=121 read=5772
  96. Total runtime: 31524.874 ms
  97.  
  98. explain (analyze, buffers)
  99. SELECT title
  100. FROM ja_jobs
  101. WHERE title ILIKE 'Hislop 13035%'
  102. AND clientid = 2565
  103. AND time_job > 1382496599
  104. ORDER BY title
  105. LIMIT 10;
  106.  
  107.  
  108. Limit (cost=2275.53..2275.55 rows=9 width=20) (actual time=3492.479..3492.483 rows=1 loops=1)
  109. Buffers: shared hit=4940 read=448
  110. I/O Timings: read=83.285
  111. -> Sort (cost=2275.53..2275.55 rows=9 width=20) (actual time=3492.475..3492.477 rows=1 loops=1)
  112. Sort Key: "title"
  113. Sort Method: quicksort Memory: 25kB
  114. Buffers: shared hit=4940 read=448
  115. I/O Timings: read=83.285
  116. -> Bitmap Heap Scan on "ja_jobs" (cost=391.62..2275.38 rows=9 width=20) (actual time=3492.460..3492.462 rows=1 loops=1)
  117. Recheck Cond: (("title")::"text" ~~* 'Hislop Street Clinic 2513035%'::"text")
  118. Filter: (("time_job" > 1382496599) AND ("clientid" = 2565))
  119. Buffers: shared hit=4940 read=448
  120. I/O Timings: read=83.285
  121. -> Bitmap Index Scan on "ix_jobs_trgm_gin" (cost=0.00..391.62 rows=482 width=0) (actual time=3492.427..3492.427 rows=1 loops=1)
  122. Index Cond: (("title")::"text" ~~* 'Hislop 13035%'::"text")
  123. Buffers: shared hit=4939 read=448
  124. I/O Timings: read=83.285
  125. Total runtime: 3492.531 ms
  126.  
  127. SELECT * FROM pg_stat_user_indexes
  128.  
  129. ALTER ja_jobs
  130. ALTER clientid SET STATISTICS 1000
  131. , ALTER time_job SET STATISTICS 1000
  132. , ALTER title SET STATISTICS 1000;
  133.  
  134. SELECT title
  135. FROM ja_jobs
  136. WHERE title ILIKE 'Hislop 13035%'
  137. AND clientid = 2565
  138. AND time_job > 1382496599
  139. LIMIT 10; -- no ORDER BY
  140.  
  141. CREATE INDEX ix_ja_jobs_special_idx ON public.ja_jobs
  142. (clientid, title varchar_pattern_ops, time_job);
  143.  
  144. CREATE INDEX ix_ja_jobs_special_idx ON public.ja_jobs
  145. (clientid, lower(title) varchar_pattern_ops, time_job);
  146.  
  147. SELECT title
  148. FROM ja_jobs
  149. WHERE lower(title) LIKE lower('Hislop 13035%')
  150. AND clientid = 2565
  151. AND time_job > 1382496599
  152. LIMIT 10;
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!