Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sort

Sort

From
"Nathan Barnett"
Date:
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

Re: Sort

From
"Mitch Vincent"
Date:
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
>
>

RE: Sort

From
"Nathan Barnett"
Date:
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
>
>

Re: Sort

From
"Mitch Vincent"
Date:
----- 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
> >
> >
>
>
>