Sample Header Ad - 728x90

Best way of SELECT on large table with filter to filter out around 1m rows with indexes

0 votes
1 answer
657 views
I have a table billing_billcycleorders that contains a foreign key billing_cycle_id. There are around 0.9m records for a particular billing_cycle_id. I want to select the data in the chunks for ~5000 (or any best way possible). The query result is exponentially increasing for values of billing_cycle_id with the higher number of records. I have run EXPLAIN ANALYZE on the query
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, format text) 
SELECT DISTINCT 
    "billing_billcycleorders"."id", 
    "billing_billcycleorders"."created", 
    "billing_billcycleorders"."updated", 
    "billing_billcycleorders"."deleted", 
    "billing_billcycleorders"."deleted_date", 
    "billing_billcycleorders"."billing_cycle_id", 
    "billing_billcycleorders"."order_id", 
    "billing_billcycleorders"."pickrr_awb", 
    "billing_billcycleorders"."amount", 
    "billing_billcycleorders"."delivery_bill", 
    "billing_billcycleorders"."rto_bill", 
    "billing_billcycleorders"."pickup_bill", 
    "billing_billcycleorders"."surcharge", 
    "billing_billcycleorders"."cod_bill", 
    "billing_billcycleorders"."qc_bill", 
    "billing_billcycleorders"."qcf_bill", 
    "billing_billcycleorders"."secure_shipment_charge", 
    "billing_billcycleorders"."cod_amount", 
    "billing_billcycleorders"."meta_details" 
FROM 
    "billing_billcycleorders" 
WHERE 
    (
        "billing_billcycleorders"."billing_cycle_id" = 685081 AND "billing_billcycleorders"."id" > 0
    ) 
ORDER BY "billing_billcycleorders"."id" ASC 
LIMIT 1000
Explain Analyze: https://explain.depesz.com/s/k9rq7
Asked by sirajalam049 (353 rep)
Dec 24, 2022, 12:36 PM
Last activity: Dec 25, 2022, 02:25 AM