Re: [PERFORM] optimizing query - Mailing list pgsql-general

hi Stephan, hi Tom,

sort_mem was at its default: 1024. I increased it, and the query takes
even longer (~ 36 secs). I tried two different values: 4096 and 8192,
this last time I reduced the shared_buffers to 25600 (--> ~ 37 secs).
Another point is: after a vacuum, the cost would slightly increase.

would it help to cluster the index? but as I am using several indexes I
find it difficult to decide on which index to cluster.

(I paste the output from vacuum full verbose analyze)

Thanks!
Chantal


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

INFO:  --Relation public.disease_occurrences_puid--
INFO:  Pages 2079: Changed 0, reaped 0, Empty 0, New 0; Tup 471915: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail.
Space 648/648; EndEmpty/Avail. Pages 0/1.
         CPU 0.02s/0.05u sec elapsed 0.07 sec.
INFO:  Index disease_occpd_puid_i: Pages 1036; Tuples 471915.
         CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  Index disease_id_puid_uni: Pages 1297; Tuples 471915.
         CPU 0.03s/0.05u sec elapsed 0.23 sec.
INFO:  Rel disease_occurrences_puid: Pages: 2079 --> 2079; Tuple(s)
moved: 0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing public.disease_occurrences_puid

INFO:  --Relation public.gene_occurrences_puid--
INFO:  Pages 1495: Changed 0, reaped 0, Empty 0, New 0; Tup 339347: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 32, MaxLen 32; Re-using: Free/Avail.
Space 648/648; EndEmpty/Avail. Pages 0/1.
         CPU 0.01s/0.04u sec elapsed 0.05 sec.
INFO:  Index gene_occpd_puid_i: Pages 746; Tuples 339347.
         CPU 0.01s/0.02u sec elapsed 0.03 sec.
INFO:  Index gene_id_puid_uni: Pages 934; Tuples 339347.
         CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO:  Rel gene_occurrences_puid: Pages: 1495 --> 1495; Tuple(s) moved: 0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Analyzing public.gene_occurrences_puid

INFO:  --Relation public.disease--
INFO:  Pages 1522: Changed 0, reaped 0, Empty 0, New 0; Tup 164597: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 232; Re-using: Free/Avail.
Space 56920/38388; EndEmpty/Avail. Pages 0/603.
         CPU 0.00s/0.04u sec elapsed 0.04 sec.
INFO:  Index disease_name_i: Pages 1076; Tuples 164597.
         CPU 0.05s/0.02u sec elapsed 0.18 sec.
INFO:  Index disease_pkey: Pages 364; Tuples 164597.
         CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO:  Index disease_uni: Pages 1168; Tuples 164597.
         CPU 0.08s/0.04u sec elapsed 0.22 sec.
INFO:  Rel disease: Pages: 1522 --> 1521; Tuple(s) moved: 75.
         CPU 0.00s/0.03u sec elapsed 0.04 sec.
INFO:  Index disease_name_i: Pages 1077; Tuples 164597: Deleted 75.
         CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  Index disease_pkey: Pages 364; Tuples 164597: Deleted 75.
         CPU 0.01s/0.02u sec elapsed 0.02 sec.
INFO:  Index disease_uni: Pages 1168; Tuples 164597: Deleted 75.
         CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  --Relation pg_toast.pg_toast_7114632--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_7114632_index: Pages 1; Tuples 0.
         CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  Analyzing public.disease

INFO:  --Relation public.gene--
INFO:  Pages 1566: Changed 0, reaped 0, Empty 0, New 0; Tup 218085: Vac
0, Keep/VTL 0/0, UnUsed 0, MinLen 44, MaxLen 348; Re-using: Free/Avail.
Space 48692/25408; EndEmpty/Avail. Pages 0/365.
         CPU 0.01s/0.04u sec elapsed 0.04 sec.
INFO:  Index gene_pkey: Pages 481; Tuples 218085.
         CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  Index gene_uni: Pages 1038; Tuples 218085.
         CPU 0.04s/0.01u sec elapsed 0.19 sec.
INFO:  Index gene_name_uni: Pages 917; Tuples 218085.
         CPU 0.06s/0.00u sec elapsed 0.15 sec.
INFO:  Rel gene: Pages: 1566 --> 1564; Tuple(s) moved: 230.
         CPU 0.01s/0.06u sec elapsed 0.11 sec.
INFO:  Index gene_pkey: Pages 482; Tuples 218085: Deleted 230.
         CPU 0.00s/0.03u sec elapsed 0.02 sec.
INFO:  Index gene_uni: Pages 1041; Tuples 218085: Deleted 230.
         CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO:  Index gene_name_uni: Pages 918; Tuples 218085: Deleted 230.
         CPU 0.00s/0.04u sec elapsed 0.03 sec.
INFO:  --Relation pg_toast.pg_toast_7114653--
INFO:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0.
         CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index pg_toast_7114653_index: Pages 1; Tuples 0.
         CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  Analyzing public.gene

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++