Обсуждение: Sort
I'm currently using v7.0.2 of PostgreSQL.
I have a query that performs a group by on three columns. The EXPLAIN of
the query is as follows:
Aggregate (cost=4116.05..4125.47 rows=94 width=24)
-> Group (cost=4116.05..4123.12 rows=942 width=24)
-> Sort (cost=4116.05..4116.05 rows=942 width=24)
-> Nested Loop (cost=0.00..4069.52 rows=942 width=24)
-> Seq Scan on click (cost=0.00..15.42 rows=942
width=8)
-> Index Scan using impression_pkey on impression
(cost=0.00..4.29 rows=1 width=16)
I need to speed up this query. I have already created an index on the three
columns query_idx(columna, columnb,columnc). Is there some other index that
I could add which would speed up the query. There is a high frequency in
the table of each group. Each group probably makes up 10% of the table.
Does this force a sequence scan when sorting and grouping? Basically just
looking for suggestions.
----------------
Nathan Barnett
Show the query and maybe someone could help :-) ----- Original Message ----- From: "Nathan Barnett" <nbarnett@cellularphones.com> To: <pgsql-general@postgresql.org> Sent: Friday, July 21, 2000 2:33 PM Subject: [GENERAL] Sort > I'm currently using v7.0.2 of PostgreSQL. > > I have a query that performs a group by on three columns. The EXPLAIN of > the query is as follows: > > Aggregate (cost=4116.05..4125.47 rows=94 width=24) > -> Group (cost=4116.05..4123.12 rows=942 width=24) > -> Sort (cost=4116.05..4116.05 rows=942 width=24) > -> Nested Loop (cost=0.00..4069.52 rows=942 width=24) > -> Seq Scan on click (cost=0.00..15.42 rows=942 > width=8) > -> Index Scan using impression_pkey on impression > (cost=0.00..4.29 rows=1 width=16) > > I need to speed up this query. I have already created an index on the three > columns query_idx(columna, columnb,columnc). Is there some other index that > I could add which would speed up the query. There is a high frequency in > the table of each group. Each group probably makes up 10% of the table. > Does this force a sequence scan when sorting and grouping? Basically just > looking for suggestions. > > > ---------------- > Nathan Barnett > >
Here is the query:
SELECT Impression.AdNumber_AdNum,
Impression.Webmaster_WebmasterNum,
Impression.Banner_BannerNum, COUNT(Click.ClickNum)
AS ClickCount
FROM Impression INNER JOIN
Click ON
Impression.ImpressionNum = Click.Impression_ImpressionNum
GROUP BY Impression.AdNumber_AdNum,
Impression.Webmaster_WebmasterNum,
Impression.Banner_BannerNum
-----------------
Nathan Barnett
-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Mitch Vincent
Sent: Friday, July 21, 2000 2:48 PM
To: Nathan Barnett; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sort
Show the query and maybe someone could help :-)
----- Original Message -----
From: "Nathan Barnett" <nbarnett@cellularphones.com>
To: <pgsql-general@postgresql.org>
Sent: Friday, July 21, 2000 2:33 PM
Subject: [GENERAL] Sort
> I'm currently using v7.0.2 of PostgreSQL.
>
> I have a query that performs a group by on three columns. The EXPLAIN of
> the query is as follows:
>
> Aggregate (cost=4116.05..4125.47 rows=94 width=24)
> -> Group (cost=4116.05..4123.12 rows=942 width=24)
> -> Sort (cost=4116.05..4116.05 rows=942 width=24)
> -> Nested Loop (cost=0.00..4069.52 rows=942 width=24)
> -> Seq Scan on click (cost=0.00..15.42 rows=942
> width=8)
> -> Index Scan using impression_pkey on impression
> (cost=0.00..4.29 rows=1 width=16)
>
> I need to speed up this query. I have already created an index on the
three
> columns query_idx(columna, columnb,columnc). Is there some other index
that
> I could add which would speed up the query. There is a high frequency in
> the table of each group. Each group probably makes up 10% of the table.
> Does this force a sequence scan when sorting and grouping? Basically just
> looking for suggestions.
>
>
> ----------------
> Nathan Barnett
>
>
----- Original Message ----- From: "Nathan Barnett" <nbarnett@cellularphones.com> To: "'Mitch Vincent'" <mitch@venux.net> Sent: Friday, July 21, 2000 3:03 PM Subject: RE: [GENERAL] Sort > Here is the query: > > SELECT Impression.AdNumber_AdNum, > Impression.Webmaster_WebmasterNum, > Impression.Banner_BannerNum, COUNT(Click.ClickNum) > AS ClickCount > FROM Impression INNER JOIN > Click ON > Impression.ImpressionNum = Click.Impression_ImpressionNum > GROUP BY Impression.AdNumber_AdNum, > Impression.Webmaster_WebmasterNum, > Impression.Banner_BannerNum > > ----------------- > Nathan Barnett > > -----Original Message----- > From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On > Behalf Of Mitch Vincent > Sent: Friday, July 21, 2000 2:48 PM > To: Nathan Barnett; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Sort > > > Show the query and maybe someone could help :-) > > ----- Original Message ----- > From: "Nathan Barnett" <nbarnett@cellularphones.com> > To: <pgsql-general@postgresql.org> > Sent: Friday, July 21, 2000 2:33 PM > Subject: [GENERAL] Sort > > > > I'm currently using v7.0.2 of PostgreSQL. > > > > I have a query that performs a group by on three columns. The EXPLAIN of > > the query is as follows: > > > > Aggregate (cost=4116.05..4125.47 rows=94 width=24) > > -> Group (cost=4116.05..4123.12 rows=942 width=24) > > -> Sort (cost=4116.05..4116.05 rows=942 width=24) > > -> Nested Loop (cost=0.00..4069.52 rows=942 width=24) > > -> Seq Scan on click (cost=0.00..15.42 rows=942 > > width=8) > > -> Index Scan using impression_pkey on impression > > (cost=0.00..4.29 rows=1 width=16) > > > > I need to speed up this query. I have already created an index on the > three > > columns query_idx(columna, columnb,columnc). Is there some other index > that > > I could add which would speed up the query. There is a high frequency in > > the table of each group. Each group probably makes up 10% of the table. > > Does this force a sequence scan when sorting and grouping? Basically just > > looking for suggestions. > > > > > > ---------------- > > Nathan Barnett > > > > > > >