PostgreSQL CROSS JOIN indexing for performance

Question

This is the second part of my question. So I have the following table,

CREATE TABLE public.main_transaction
(
  id integer NOT NULL DEFAULT nextval('main_transaction_id_seq'::regclass),
  profile_id integer NOT NULL,
  request_no character varying(18),
  user_id bigint,
  .....
  CONSTRAINT main_transaction_pkey PRIMARY KEY (id),

  CONSTRAINT fk_main_transaction_user_id FOREIGN KEY (user_id)
      REFERENCES public.jhi_user (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
      REFERENCES public.main_profile (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,

  CONSTRAINT main_transaction_profile_id_20_fk_main_profile_id FOREIGN KEY (profile_id)
      REFERENCES public.main_profile (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
)

In the table I have foreign keys in my table and I am using CROSS JOIN to link the tables by id and it is causing slow performance( returning result in 12 secs for over 1 Million row data). More precisely,I have a profile_id column in main_transaction(above) table for profile table and in profile table I have customer_id for customer table. So, my query is as following,

SELECT * FROM main_transaction t 
CROSS JOIN main_profile p 
CROSS JOIN main_customer c 
WHERE t.profile_id = p.id
AND p.user_id = c.id 
AND ( upper(t.request_no) LIKE upper(concat('%','0-90-6 12 ','%')) 
      OR upper(c.phone) LIKE upper(concat('%','0-90-6 12','%')) 
)

If you need structure of profile and customer table, I can comment.

Explain:

"Merge Join  (cost=27220.51..266464.85 rows=218 width=1692) (actual time=9399.370..9399.370 rows=0 loops=1)"
"  Merge Cond: (t.profile_id = p.id)"
"  Join Filter: ((upper((t.request_no)::text) ~~ upper(concat('%', ' 0-90-6 12 ', '%'))) OR (upper((c.phone)::text) ~~ upper(concat('%', ' 0-90-6 12, '%'))))"
"  Rows Removed by Join Filter: 1089489"
"  Buffers: shared hit=453158 read=413372, temp read=1560 written=1560"
"  ->  Index Scan using main_transaction_profile_id_idx on main_transaction t  (cost=0.43..198177.36 rows=1089489 width=1455) (actual time=0.004..3913.501 rows=1089489 loops=1)"
"        Buffers: shared hit=393656 read=410718"
"  ->  Materialize  (cost=27218.84..27645.70 rows=85372 width=237) (actual time=165.565..239.572 rows=1133650 loops=1)"
"        Buffers: shared hit=59502 read=2654, temp read=1560 written=1560"
"        ->  Sort  (cost=27218.84..27432.27 rows=85372 width=237) (actual time=165.560..188.746 rows=85368 loops=1)"
"              Sort Key: p.id"
"              Sort Method: external sort  Disk: 12480kB"
"              Buffers: shared hit=59502 read=2654, temp read=1560 written=1560"
"              ->  Merge Join  (cost=0.73..10594.24 rows=85372 width=237) (actual time=0.007..96.133 rows=85372 loops=1)"
"                    Merge Cond: (p.user_id = c.id)"
"                    Buffers: shared hit=59502 read=2654"
"                    ->  Index Scan using main_profile_e8701ad4 on main_profile p  (cost=0.29..3350.82 rows=85372 width=40) (actual time=0.003..24.892 rows=85372 loops=1)"
"                          Buffers: shared hit=29016 read=963"
"                    ->  Index Scan using main_customer_pkey on main_customer c  (cost=0.29..5963.16 rows=85506 width=197) (actual time=0.003..34.235 rows=85506 loops=1)"
"                          Buffers: shared hit=30486 read=1691"
"Planning time: 0.850 ms"
"Execution time: 9407.244 ms"

First: don't write CROSS JOIN when you actually want INNER JOIN. Second: Do you have indexes on the origin of the foreign keys? Third: Please show the output of EXPLAIN (ANALYZE, BUFFERS). Edit the question to add that information. — Laurenz Albe
– Laurenz Albe, Commented Apr 20, 2018 at 13:00
@Laurenz Albe First: OK, will do it. Second: For profile table, I only have btree index on user_id. And for customer table I have gin index for phone. Third: Will add it to the question — Sanatbek Matlatipov
– Sanatbek Matlatipov, Commented Apr 20, 2018 at 13:34
@a_horse_with_no_name. I changed query to INNER JOIN However didn't notice any performance. — Sanatbek Matlatipov
– Sanatbek Matlatipov, Commented Apr 20, 2018 at 13:49

Laurenz Albe · Accepted Answer · 2018-04-20 14:21:37Z

1

Your main problem is the OR — you can never get decent performance as long as you have an OR like this in your WHERE clause.

Rewrite the query as follows:

SELECT * FROM main_transaction t 
   JOIN main_profile p ON t.profile_id = p.id
   JOIN main_customer c ON p.user_id = c.id 
WHERE upper(t.request_no) LIKE upper(concat('%','0-90-6 12 ','%'))
UNION
SELECT * FROM main_transaction t 
   JOIN main_profile p ON t.profile_id = p.id
   JOIN main_customer c ON p.user_id = c.id 
WHERE upper(c.phone) LIKE upper(concat('%','0-90-6 12','%'));

Then make sure you have the following indexes (apart from the indexes on the ids):

CREATE INDEX ON main_transaction (profile_id);
CREATE INDEX ON main_transaction USING gin (upper(request_no) gin_trgm_ops);
CREATE INDEX ON main_profile (user_id);
CREATE INDEX ON main_customer USING gin (upper(phone) gin_trgm_ops);

That should make a difference.

edited Apr 20, 2018 at 14:21

answered Apr 20, 2018 at 13:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Sanatbek Matlatipov Over a year ago

SELECT * FROM main_transaction t JOIN main_profile p ON (t.profile_id = p.id) JOIN main_customer c ON (p.user_id = c.id) WHERE  ( 	upper(t.request_no) LIKE upper(concat('%','0-90-6 12 ','%'))         OR upper(c.phone) LIKE upper(concat('%''0-90-6 12 ','%'))	 )

I created indexes, however no better performance. What else can you suggest, If you need more detailed info, please write

joop Over a year ago

@mr.sanatbek First: remove all thes silly upper()s. they do nothing, exept making indexes unusable and things slower. And the OR list (on othogonal conditions) is a disaster, IMHO

Sanatbek Matlatipov Over a year ago

@joop inside upper() there can be letter(it is variable). Sorry for my silly example

joop Over a year ago

The literal '0-90-6 12 ' does not contain any letter, so the upper is a no-op.

Laurenz Albe Over a year ago

@joop Maybe the example was just badly chosen. Case insensitive search is not an unusual requirement.

|

Collectives™ on Stack Overflow

PostgreSQL CROSS JOIN indexing for performance

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related