1

Here is a very simple request :

    select
         ftljh."ID_TRAIN",
         ftljh."ID_JOUR",
         ftljh."ID_LEG",
         2 as "CMPT",
         ftljh."JX",
         ftljh."CAPA",
         ftljh."OFFRE",
         ftljh."RES",
         ftljh."CC_OUV"
    from public."F_TDLJ_HIST_2" ftljh
    where "ID_JOUR" between 4620 and 4650
    and "JX" between -92 and 1
    and "ID_TRAIN" in (10625,11891,11242,735,13674,14697,14852,15917)

I ran it, and got a response time of ~1min44s. You can find the whole EXPLAIN ANALYZE here : https://explain.dalibo.com/plan/g4ebc5g851h4d71b

My table public."F_TDLJ_HIST_2" is partitioned, but I ensured my request only select from one of those partitions.

I then copied this exact requested partition subtable to another schema, using CREATE TABLE ... LIKE ... and INSERT INTO ... (SELECT * FROM my_partition_subtable).

    select
         ftljh."ID_TRAIN",
         ftljh."ID_JOUR",
         ftljh."ID_LEG",
         2 as "CMPT",
         ftljh."JX",
         ftljh."CAPA",
         ftljh."OFFRE",
         ftljh."RES",
         ftljh."CC_OUV"
    from experiment."F_TDLJ_HIST_2" ftljh
    where "ID_JOUR" between 4620 and 4650
    and "JX" between -92 and 1
    and "ID_TRAIN" in (10625,11891,11242,735,13674,14697,14852,15917)

returned all my data in 11s!. The EXPLAIN ANALYZE of the request made against the copied table can be found here: https://explain.dalibo.com/plan/89geefh65e15df9d

I can't find a reason for this difference. I tried a VACUUM, a VACUUM FULL, restoring indexes, and I'm a bit out of ideas.

EDIT : My table is partitionned on ID_JOUR. One subtable contains 90 ID_JOUR (90 days). It contains ~80M rows, and weights ~50GB. My requests are tailored to only request from one subtable. The experiment."F_TDLJ_HIST_2" contains exactly the data stored in the partitionned table requested from public."F_TDLJ_HIST_2" (so experiment.F_TDLJ_HIST_2 = partitionschema."F_TDLJ_HIST_2_p4590"). The execution plan against the copied table is the second explain.dalibo link.

SECOND EDIT : I found a solution that made my request almost instant. I used the CLUSTER USING command to physically reorganize my data, which has made my query 100x faster. What mechanism could explain such an improvement ?

To be precise, I created an index using btree("ID_TRAIN", "ID_JOUR") and then used this index to cluster my table.

7
  • Welcome to the DBA.SE community. Interesting question, but the it is possibly missing a couple of details. What is your table partitioned on? Is it ID_TRAIN? How may records does the table contain? How may records in the partition? What is the execution plan of the statement run against the "copied table"? Could you click on edit and add these details to your question? Thanks.
    – John K. N.
    Sep 18, 2023 at 9:43
  • 1
    I edited my post to include the missing details !
    – Doe Jowns
    Sep 18, 2023 at 9:51
  • Perhaps it's the index bloat?
    – mustaccio
    Sep 18, 2023 at 12:23
  • Is this reproducible if you switch back and forth several times? It looks to me like the faster query found most of its needed data already in memory (mostly the filesystem cache). Turn on track_io_timing to get more clarity there.
    – jjanes
    Sep 18, 2023 at 16:52
  • Yes it was reproducible. I vary the perimeter I select every time.
    – Doe Jowns
    Sep 19, 2023 at 15:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.