Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: multi column query
Date: Thu, 13 Apr 2006 11:42:25 -0500
Message-ID: 
 <4D27CB1096EF1C408F4BFAB0046EC7B6099EC5@ausmailid.aus.pervasive.com>
Thread-Topic: [PERFORM] multi column query
Thread-Index: AcZek1yvb6Ri7m29TvG7TO2hvoi3bwAAFySAACFRyTA=
From: "Jim Nasby" <jnasby@pervasive.com>
To: "Sriram Dandapani" <sdandapani@counterpane.com>
Cc: "Pgsql-Performance \(E-mail\)" <pgsql-performance@postgresql.org>

You need to run EXPLAIN ANALYZE. Also, what's random_page_cost set to? =
And the output of \d chkpfw_tr_dy_dimension. The cost for that index =
scan looks way too high.

And please reply-all so that the list is included.

> -----Original Message-----
> From: Sriram Dandapani [mailto:sdandapani@counterpane.com]
> Sent: Wednesday, April 12, 2006 7:48 PM
> To: Jim Nasby
> Subject: RE: [PERFORM] multi column query
>=20
>=20
> I executed enable_seqscan=3Doff and then ran an explain plan on=20
> the query
>=20
> UPDATE chkpfw_tr_dy_dimension
> 			 SET summcount =3D a.summcount + b.summcount,
> 			 bytes =3D a.bytes + b.bytes,
> 			 duration =3D a.duration + b.duration
> 			 from chkpfw_tr_dy_dimension a,
> c_chkpfw_dy_tr_updates b
> 			 WHERE a.firstoccurrence =3D b.firstoccurrence
> 			AND a.customerid_id =3D b.customerid_id
> 			 AND a.sentryid_id =3D b.sentryid_id
> 			AND a.node_id =3D b.node_id
> 			 AND a.interface_id =3D b.interface_id
> 			 AND a.source_id =3D b.source_id
> 			 AND a.destination_id =3D b.destination_id
> 			 AND a.sourceport_id =3D b.sourceport_id
> 			 AND a.destinationport_id =3D b.destinationport_id
> 			 AND a.inoutbound_id =3D b.inoutbound_id
> 			 AND a.action_id =3D b.action_id
> 			 AND a.protocol_id =3D b.protocol_id
> 			 AND a.service_id =3D b.service_id
> 			 AND a.sourcezone_id =3D b.sourcezone_id
> 			 AND a.destinationzone_id =3D
> b.destinationzone_id;
>=20
>=20
>=20
> Here is the query plan
>=20
>=20
> "Nested Loop  (cost=3D200000036.18..221851442.39 rows=3D1 =
width=3D166)"
> "  ->  Merge Join  (cost=3D100000036.18..121620543.75 rows=3D1 =
width=3D96)"
> "        Merge Cond: (("outer".firstoccurrence =3D
> "inner".firstoccurrence) AND ("outer".sentryid_id =3D=20
> "inner".sentryid_id)
> AND ("outer".node_id =3D "inner".node_id))"
> "        Join Filter: (("outer".customerid_id =3D =
"inner".customerid_id)
> AND ("outer".interface_id =3D "inner".interface_id) AND=20
> ("outer".source_id
> =3D "inner".source_id) AND ("outer".destination_id =3D
> "inner".destination_id) AND ("outer".sourceport_id =3D "inner".s (..)"
> "        ->  Index Scan using chkpfw_tr_dy_idx1 on
> chkpfw_tr_dy_dimension a  (cost=3D0.00..21573372.84 rows=3D6281981
> width=3D88)"
> "        ->  Sort  (cost=3D100000036.18..100000037.38 rows=3D480=20
> width=3D136)"
> "              Sort Key: b.firstoccurrence, b.sentryid_id, b.node_id"
> "              ->  Seq Scan on c_chkpfw_dy_tr_updates b
> (cost=3D100000000.00..100000014.80 rows=3D480 width=3D136)"
> "  ->  Seq Scan on chkpfw_tr_dy_dimension
> (cost=3D100000000.00..100168078.81 rows=3D6281981 width=3D70)"
>=20
> -----Original Message-----
> From: Jim C. Nasby [mailto:jnasby@pervasive.com]=20
> Sent: Wednesday, April 12, 2006 5:44 PM
> To: Sriram Dandapani
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] multi column query
>=20
> On Wed, Apr 12, 2006 at 05:32:32PM -0700, Sriram Dandapani wrote:
> > Hi
> >=20
> > When I update a table that has 20 columns and the where clause
> includes
> > 16 of the columns (this is a data warehousing type update=20
> on aggregate
> > fields),
> >=20
> > The bitmap scan is not used by the optimizer. The table is=20
> indexed on
> 3
> > of the 20 fields. The update takes really long to finish (on a 6
> million
> > row table)
> >=20
> > Do I need to do some "magic" with configuration to turn on bitmap
> scans.
>=20
> No. What's explain analyze of the query show? What's it doing now?
> Seqscan? You might try set enable_seqscan=3Doff and see what that =
does.
> --=20
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software      http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461
>=20