I just want to retrieve the data for specific location of customers with their ever first and last purchases made in the system.
Table details :
CREATE TABLE customer_location ( id UUID, location_id UUID, customer_id UUID ); CREATE TABLE customer_receipt ( id UUID, receipt_id UUID, customer_id UUID ); CREATE TABLE receipt_product ( id UUID, receipt_id UUID, product_id UUID, txn_dt_tm Timestamp, status TEXT, is_active boolean);
SELECT cr.customer_id, min(txn_dt_tm), max(txn_dt_tm) FROM customer_location cl JOIN customer_receipt cr ON cl.customer_id = cr.customer_id AND cl.location_id = 'UUID here' JOIN receipt_product rp ON rp.receipt_id = cr.receipt_id AND status = 'PURCHASED' AND is_active = true GROUP BY cr.customer_id
receipt_product : Index applied on (status and is_active).
Other tables are implemented with the primary key index.
Though the query engine performs the SEQ scan rather index. Since I see this could be the huge load for index search and thus the engine picked up the Sequential scan. The time taken to load the data is approximately 36 Seconds which is a huge load time.
Here is the query plan http://explain.depesz.com/s/5hV.
HashAggregate (cost=1023419.340..1023425.980 rows=664 width=24) (actual time=42811.045..42811.152 rows=142 loops=1) -> Hash Right Join (cost=464863.230..1011575.600 rows=1579165 width=24) (actual time=17306.921..41755.433 rows=1101749 loops=1) Hash Cond: (receipt_product.receipt_id = customer_receipt.receipt_id) -> Seq Scan on receipt_product (cost=0.000..405877.220 rows=6981890 width=24) (actual time=0.051..14179.158 rows=6890896 loops=1) Filter: (is_active AND (status <> 'PURCHASED'::uuid)) Rows Removed by Filter: 2909925 -> Hash (cost=434327.670..434327.670 rows=1579165 width=32) (actual time=17306.658..17306.658 rows=1101749 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 17257kB -> Hash Right Join (cost=44.660..434327.670 rows=1579165 width=32) (actual time=2345.366..16229.130 rows=1101749 loops=1) Hash Cond: (customer_receipt.customer_id = customer_location.customer_id) Join Filter: (customer_location.location_id = 'location_id'::uuid) Rows Removed by Join Filter: 7182859 -> Seq Scan on customer_receipt (cost=0.000..299670.480 rows=8283848 width=32) (actual time=0.004..7416.387 rows=8283831 loops=1) -> Hash (cost=33.180..33.180 rows=918 width=32) (actual time=1.551..1.551 rows=918 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 58kB -> Seq Scan on customer_location (cost=0.000..33.180 rows=918 width=32) (actual time=0.006..0.759 rows=918 loops=1)
By re-writing the same query as postgres function which would iterate every customer of location and would fetch the max and min date of that customer. Which performs 6 times faster ( 6 seconds aprx. ) but when data grows huge, that would take time.
I strongly hope to see the false bucket here also like to have the experts' Suggestions.
I tried disabled the seq scan. still that takes same amount of time with index scans. So Query plan picked up the right way to execute. What are all the possibilities to reduce the execution time here. Is there any settings which would deal with the execution speed.
I also adding the query tuning setting parameters here from my DB.
cpu_index_tuple_cost 0.005 cpu_operator_cost 0.0025 cpu_tuple_cost 0.01 effective_cache_size 464300 random_page_cost 1 seq_page_cost 1