pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with large query
6+ messages / 3 participants
[nested] [flat]
* Problem with large query
@ 2004-09-08 13:49 Marc Cousin <mcousin@sigma.fr>
0 siblings, 1 reply; 6+ messages in thread
From: Marc Cousin @ 2004-09-08 13:49 UTC (permalink / raw)
To: pgsql-performance
Hi. I hope I'm not asking a too trivial question here...
I'm having trouble with a (quite big) query, and can't find a way to make it
faster.
Here is the information :
Tables :
============================
sces_vte -> 2753539 rows
sces_art -> 602327
sces_fsf -> 8126
sces_frc -> 7763
sces_tps -> 38
sces_gtr -> 35
Query :
===========================
SELECT
sces_gtr_art.gtr_cod,
sces_gtr_art.gtr_lib,
sces_frc_art.fou_cod,
sces_frc_art.fou_lib,
sces_tps.tps_annee_mois,
TO_NUMBER('200401','999999'),
TO_NUMBER('200405','999999'),
sces_tps.tps_libc,
sum(sces_vte.vte_mnt),
sum(sces_vte.vte_qte),
sum(sces_vte.vte_ton),
sces_famille.fsf_codfam,
sces_famille.fsf_lib,
sces_s_famille.fsf_codsfm,
sces_s_famille.fsf_lib
FROM
sces_vte,
sces_art,
sces_fsf sces_famille,
sces_fsf sces_s_famille,
sces_frc sces_frc_art,
sces_tps,
sces_gtr sces_gtr_art
WHERE
( sces_famille.fsf_codfam=sces_s_famille.fsf_codfam )
AND ( sces_famille.fsf_codseg= 0 and sces_famille.fsf_codsfm = 0 )
AND ( sces_vte.tps_annee_mois=sces_tps.tps_annee_mois )
AND ( sces_vte.art_cod=sces_art.art_cod and
sces_vte.dos_cod=sces_art.dos_cod )
AND ( sces_gtr_art.gtr_cod=sces_frc_art.gtr_cod )
AND ( sces_frc_art.gtr_cod=sces_art.gtr_cod and
sces_frc_art.fou_cod=sces_art.fou_cod )
AND ( sces_s_famille.fsf_codfam=sces_art.fsf_codfam and
sces_s_famille.fsf_codsfm=sces_art.fsf_codsfm )
AND ( sces_s_famille.fsf_codseg = 0 )
AND (
( ( ( sces_tps.tps_annee_mois ) >= ( TO_NUMBER('200401','999999') ) and
( sces_tps.tps_annee_mois ) <= (
TO_NUMBER('200405','999999') )
)
OR
(
( sces_tps.tps_annee_mois ) >= ( TO_NUMBER('200401','999999') )-100 and
( sces_tps.tps_annee_mois ) <= (
TO_NUMBER('200405','999999') )-100
) )
AND ( sces_gtr_art.gtr_cod in (2))
)
GROUP BY
sces_gtr_art.gtr_cod,
sces_gtr_art.gtr_lib,
sces_frc_art.fou_cod,
sces_frc_art.fou_lib,
sces_tps.tps_annee_mois,
TO_NUMBER('200401','999999'),
TO_NUMBER('200405','999999'),
sces_tps.tps_libc,
sces_famille.fsf_codfam,
sces_famille.fsf_lib,
sces_s_famille.fsf_codsfm,
sces_s_famille.fsf_lib
Explain Analyze Plan :
====================================
GroupAggregate (cost=27161.91..27938.72 rows=16354 width=280) (actual time=484509.210..544436.148 rows=4115 loops=1)
-> Sort (cost=27161.91..27202.79 rows=16354 width=280) (actual time=484496.188..485334.151 rows=799758 loops=1)
Sort Key: sces_gtr_art.gtr_cod, sces_gtr_art.gtr_lib, sces_frc_art.fou_cod, sces_frc_art.fou_lib, sces_tps.tps_annee_mois, 200401::numeric, 200405::numeric, sces_tps.tps_libc, sces_famille.fsf_codfam, sces_famille.fsf_lib, sces_s_famille.fsf_codsfm, sces_s_famille.fsf_lib
-> Merge Join (cost=25727.79..26017.34 rows=16354 width=280) (actual time=58945.821..69321.146 rows=799758 loops=1)
Merge Cond: (("outer".fsf_codfam = "inner".fsf_codfam) AND ("outer".fsf_codsfm = "inner".fsf_codsfm))
-> Sort (cost=301.36..304.60 rows=1298 width=83) (actual time=27.926..28.256 rows=332 loops=1)
Sort Key: sces_s_famille.fsf_codfam, sces_s_famille.fsf_codsfm
-> Seq Scan on sces_fsf sces_s_famille (cost=0.00..234.24 rows=1298 width=83) (actual time=0.042..19.124 rows=1341 loops=1)
Filter: (fsf_codseg = 0::numeric)
-> Sort (cost=25426.43..25448.05 rows=8646 width=225) (actual time=58917.106..59693.810 rows=799758 loops=1)
Sort Key: sces_art.fsf_codfam, sces_art.fsf_codsfm
-> Merge Join (cost=24726.32..24861.08 rows=8646 width=225) (actual time=19036.709..29404.943 rows=799758 loops=1)
Merge Cond: ("outer".tps_annee_mois = "inner".tps_annee_mois)
-> Sort (cost=2.49..2.53 rows=17 width=23) (actual time=0.401..0.428 rows=20 loops=1)
Sort Key: sces_tps.tps_annee_mois
-> Seq Scan on sces_tps (cost=0.00..2.14 rows=17 width=23) (actual time=0.068..0.333 rows=20 loops=1)
Filter: (((tps_annee_mois >= 200301::numeric) OR (tps_annee_mois >= 200401::numeric)) AND ((tps_annee_mois <= 200305::numeric) OR (tps_annee_mois >= 200401::numeric)) AND ((tps_annee_mois >= 200301::numeric) OR (tps_annee_mois <= 200405::numeric)) AND ((tps_annee_mois <= 200305::numeric) OR (tps_annee_mois <= 200405::numeric)))
-> Sort (cost=24723.83..24747.97 rows=9656 width=214) (actual time=19036.223..19917.214 rows=799757 loops=1)
Sort Key: sces_vte.tps_annee_mois
-> Nested Loop (cost=21825.09..24084.74 rows=9656 width=214) (actual time=417.603..8644.294 rows=399879 loops=1)
-> Nested Loop (cost=21825.09..21837.50 rows=373 width=195) (actual time=417.444..672.741 rows=14158 loops=1)
-> Seq Scan on sces_gtr sces_gtr_art (cost=0.00..1.44 rows=1 width=40) (actual time=0.026..0.085 rows=1 loops=1)
Filter: (gtr_cod = 2::numeric)
-> Merge Join (cost=21825.09..21832.34 rows=373 width=165) (actual time=417.400..568.247 rows=14158 loops=1)
Merge Cond: ("outer".fsf_codfam = "inner".fsf_codfam)
-> Sort (cost=255.24..255.30 rows=24 width=74) (actual time=16.597..16.692 rows=106 loops=1)
Sort Key: sces_famille.fsf_codfam
-> Seq Scan on sces_fsf sces_famille (cost=0.00..254.69 rows=24 width=74) (actual time=0.029..15.971 rows=155 loops=1)
Filter: ((fsf_codseg = 0::numeric) AND (fsf_codsfm = 0::numeric))
-> Sort (cost=21569.85..21571.64 rows=715 width=91) (actual time=400.631..416.871 rows=14162 loops=1)
Sort Key: sces_art.fsf_codfam
-> Nested Loop (cost=0.00..21535.95 rows=715 width=91) (actual time=1.320..230.975 rows=14162 loops=1)
-> Seq Scan on sces_frc sces_frc_art (cost=0.00..182.75 rows=728 width=51) (actual time=1.195..14.316 rows=761 loops=1)
Filter: (2::numeric = gtr_cod)
-> Index Scan using ind_art_02 on sces_art (cost=0.00..29.24 rows=7 width=61) (actual time=0.040..0.160 rows=19 loops=761)
Index Cond: ((2::numeric = sces_art.gtr_cod) AND ("outer".fou_cod = sces_art.fou_cod))
-> Index Scan using idx_vte_02 on sces_vte (cost=0.00..6.01 rows=1 width=62) (actual time=0.037..0.259 rows=28 loops=14158)
Index Cond: ((sces_vte.art_cod = "outer".art_cod) AND (sces_vte.dos_cod = "outer".dos_cod))
Total runtime: 545435.989 ms
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with large query
@ 2004-09-08 14:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Cousin <mcousin@sigma.fr>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2004-09-08 14:40 UTC (permalink / raw)
To: Marc Cousin <mcousin@sigma.fr>; +Cc: pgsql-performance
Marc Cousin <mcousin@sigma.fr> writes:
> I'm having trouble with a (quite big) query, and can't find a way to make it
> faster.
Seems like it might help if the thing could use a HashAggregate instead
of sort/group. Numeric is not hashable, so having those TO_NUMBER
constants in GROUP BY destroys this option instantly ... but why in the
world are you grouping by constants anyway? You didn't say what the
datatypes of the other columns were...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with large query
@ 2004-09-08 14:47 Adam Sah <asah@speakeasy.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Adam Sah @ 2004-09-08 14:47 UTC (permalink / raw)
To: pgsql-performance
by the way, this reminds me: I just ran a performance study at a company doing
an oracle-to-postgres conversion, and FYI converting from numeric and decimal
to integer/bigint/real saved roughly 3x on space and 2x on performance.
Obviously, YMMV.
adam
Tom Lane wrote:
> Marc Cousin <mcousin@sigma.fr> writes:
>
>>I'm having trouble with a (quite big) query, and can't find a way to make it
>>faster.
>
>
> Seems like it might help if the thing could use a HashAggregate instead
> of sort/group. Numeric is not hashable, so having those TO_NUMBER
> constants in GROUP BY destroys this option instantly ... but why in the
> world are you grouping by constants anyway? You didn't say what the
> datatypes of the other columns were...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with large query
@ 2004-09-08 14:49 Marc Cousin <mcousin@sigma.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Marc Cousin @ 2004-09-08 14:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
The query has been generated by business objects ... i'ill try to suggest to the developpers to remove this constant (if they can)...
The fields used by the sort are of type numeric(6,0) or (10,0) ...
Could it be better if the fields were integer or anything else ?
On Wednesday 08 September 2004 16:40, you wrote:
> Marc Cousin <mcousin@sigma.fr> writes:
> > I'm having trouble with a (quite big) query, and can't find a way to make it
> > faster.
>
> Seems like it might help if the thing could use a HashAggregate instead
> of sort/group. Numeric is not hashable, so having those TO_NUMBER
> constants in GROUP BY destroys this option instantly ... but why in the
> world are you grouping by constants anyway? You didn't say what the
> datatypes of the other columns were...
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with large query
@ 2004-09-08 14:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Cousin <mcousin@sigma.fr>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-09-08 14:56 UTC (permalink / raw)
To: Marc Cousin <mcousin@sigma.fr>; +Cc: pgsql-performance
Marc Cousin <mcousin@sigma.fr> writes:
> The query has been generated by business objects ... i'ill try to suggest to the developpers to remove this constant (if they can)...
> The fields used by the sort are of type numeric(6,0) or (10,0) ...
> Could it be better if the fields were integer or anything else ?
integer or bigint would be a WHOLE lot faster. I'd venture that
comparing two numerics is order of a hundred times slower than
comparing two integers.
Even if you don't want to change the fields on-disk, you might think
about casting them all to int/bigint in the query.
Another thing that might or might not be easy is to change the order of
the GROUP BY items so that the fields with the largest number of
distinct values are listed first. If two rows are distinct at the first
column, the sorting comparison doesn't even have to look at the
remaining columns ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with large query
@ 2004-09-08 15:17 Marc Cousin <mcousin@sigma.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Marc Cousin @ 2004-09-08 15:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Wednesday 08 September 2004 16:56, you wrote:
> Marc Cousin <mcousin@sigma.fr> writes:
> > The query has been generated by business objects ... i'ill try to suggest to the developpers to remove this constant (if they can)...
> > The fields used by the sort are of type numeric(6,0) or (10,0) ...
> > Could it be better if the fields were integer or anything else ?
>
> integer or bigint would be a WHOLE lot faster. I'd venture that
> comparing two numerics is order of a hundred times slower than
> comparing two integers.
>
> Even if you don't want to change the fields on-disk, you might think
> about casting them all to int/bigint in the query.
>
> Another thing that might or might not be easy is to change the order of
> the GROUP BY items so that the fields with the largest number of
> distinct values are listed first. If two rows are distinct at the first
> column, the sorting comparison doesn't even have to look at the
> remaining columns ...
>
> regards, tom lane
>
Thanks. I've just had confirmation that they can remove the two constants (allready won 100 seconds thanks to that)
I've tried the cast, and got down to 72 seconds.
So now we're going to try to convert the fields to int or bigint.
Thanks a lot for your help and time.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-09-08 15:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-08 13:49 Problem with large query Marc Cousin <mcousin@sigma.fr>
2004-09-08 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-08 14:47 ` Adam Sah <asah@speakeasy.net>
2004-09-08 14:49 ` Marc Cousin <mcousin@sigma.fr>
2004-09-08 14:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-08 15:17 ` Marc Cousin <mcousin@sigma.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox