On Wed 31 May 2006 19:23, Sven Anderson wrote:
> Hi all,
>
> I want to share with you an interesting experience I just had.
>
> I did the following SELECT:
>
> SELECT "ip_dst",SUM("bytes"),SUM("packets"),SUM("flows") FROM
> "tcom_v5_20060530" WHERE "stamp_inserted">='2006-05-30 00:00:00' AND
> "stamp_inserted"<'2006-05-30 02:00:00' AND "port_src"='53' AND
> "port_dst"='53' AND "ip_src"='xxx.xxx.xxx.xxx' GROUP BY "ip_dst" ORDER BY
> SUM("bytes") DESC LIMIT '10';
>
> which seemed to never end (>30 minutes).
>
> I had only two indexes on this table:
> "tcom_v5_20060530_idx" btree (ip_src, ip_dst, stamp_inserted)
> "tcom_v5_20060530_stamp_idx" btree (stamp_inserted)
>
> After dropping the tcom_v5_20060530_idx, the query was answered in 20
> seconds.When is the last time you did a VACUUM ANALYZE? -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
pgpUrzZXNexl7.pgp
Description: PGP signature
_______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
