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
Last activity: Dec 25, 2022, 02:25 AM