X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 865C8D1D55A for ; Wed, 24 Mar 2004 13:59:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 02220-05 for ; Wed, 24 Mar 2004 09:59:14 -0400 (AST) Received: from sphere.istructure.com (istructure.com [24.199.154.122]) by svr1.postgresql.org (Postfix) with ESMTP id 6E669D1C4ED for ; Wed, 24 Mar 2004 09:59:10 -0400 (AST) Received: from isswoodyxp (isswoody.istructure.com [192.168.177.111]) by sphere.istructure.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id FWMJ0ST5; Wed, 24 Mar 2004 08:59:07 -0500 From: "George Woodring" To: Subject: Re: Help with query plan inconsistencies Date: Wed, 24 Mar 2004 08:59:07 -0500 Message-ID: <000f01c411a8$2ce72420$6fb1a8c0@istructure.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 In-Reply-To: Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/348 X-Sequence-Number: 6204 I currently have it set up to vacuum/analyze every 2 hours. However my QUERY PLAN #1 & #2 in my example I ran my explain immediately after a vacuum/analyze. -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Joseph Shraibman Sent: Tuesday, March 23, 2004 2:17 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Help with query plan inconsistencies I'm going to ask because someone else surely will: Do you regularily vacuum/analyze the database? Woody Woodring wrote: > Hello, >=20 > I am using postgres 7.4.2 as a backend for geocode data for a mapping=20 > application. My question is why can't I get a consistent use of my=20 > indexes during a query, I tend to get a lot of seq scan results. >=20 > I use a standard query: >=20 > SELECT lat, long, mac, status FROM ( > SELECT text(mac) as mac, lat long, CASE status WHEN 0 THEN 0 WHEN 1=20 > THEN 1 ELSE -1 END > as status FROM cable_billing LEFT OUTER JOIN davic USING(mac)=20 > WHERE boxtype=3D'd' )AS FOO WHERE (long>=3DX1) AND (long<=3DX2) AND=20 > (lat>=3DY1) AND (lat<=3DY2) >=20 > Where X1,X2,Y1,Y2 are the coordinates for the rectangle of the map=20 > viewing area. >=20 > QUERY PLAN #1 & #2 are from when I get a view from 10 miles out,=20 > sometimes it uses the index(#1) and most of the time not(#2). I do=20 > run into plans that seq scan both sides of the join. >=20 > QUERY PLAN #3 is when I view from 5 miles out, and I have much greater=20 > chance of getting index scans ( about 90% of the time). >=20 > I have listed information about the database below. >=20 > Cable_billing ~500,000 rows updated once per day > Davic ~500,000 rows, about 100 rows update per minute >=20 > Any info or suggestions would be appreciated. >=20 > Woody >=20 >=20 > twc-ral-overview=3D# \d cable_billing; > Table "public.cable_billing" > Column | Type | Modifiers=20 > -----------------+------------------------+----------- > cable_billingid | integer | not null > mac | macaddr | not null > account | integer |=20 > number | character varying(10) |=20 > address | character varying(200) |=20 > region | character varying(30) |=20 > division | integer |=20 > franchise | integer |=20 > node | character varying(10) |=20 > lat | numeric |=20 > long | numeric |=20 > trunk | character varying(5) |=20 > ps | character varying(5) |=20 > fd | character varying(5) |=20 > le | character varying(5) |=20 > update | integer |=20 > boxtype | character(1) |=20 > Indexes: cable_billing_pkey primary key btree (mac), > cable_billing_account_index btree (account), > cable_billing_lat_long_idx btree (lat, long), > cable_billing_node_index btree (node), > cable_billing_region_index btree (region) >=20 > twc-ral-overview=3D# \d davic > Table "public.davic" > Column | Type | Modifiers=20 > ---------+-----------------------+----------- > davicid | integer | not null > mac | macaddr | not null > source | character varying(20) |=20 > status | smallint |=20 > updtime | integer |=20 > type | character varying(10) |=20 > avail1 | integer |=20 > Indexes: davic_pkey primary key btree (mac) >=20 >=20 >=20 > twc-ral-overview=3D# vacuum analyze; > VACUUM > twc-ral-overview=3D# explain analyze SELECT lat, long, mac, status FROM= =20 > (SELECT text(mac) as mac, lat, long, CASE status WHEN 0 THEN 0 WHEN 1=20 > THEN 1 ELSE -1 END as status FROM cable_billing LEFT OUTER JOIN davic=20 > USING(mac) WHERE boxtype=3D'd') AS foo WHERE (long>=3D-78.70723462816063= )=20 > AND > (long<=3D-78.53096764204116) AND (lat>=3D35.57411187866667) AND > (lat<=3D35.66366331376857); > QUERY PLAN #1 >=20 > ---------------------------------------------------------------------- > ------ > ---------------------------------------------------------------------------- > ----- > Nested Loop Left Join (cost=3D0.00..23433.18 rows=3D1871 width=3D34) (a= ctual > time=3D0.555..5095.434 rows=3D3224 loops=3D1) > -> Index Scan using cable_billing_lat_long_idx on cable_billing > (cost=3D0.00..12145.85 rows=3D1871 width=3D32) (actual time=3D0.431..249.= 931 > rows=3D3224 loops=3D1) > Index Cond: ((lat >=3D 35.57411187866667) AND (lat <=3D > 35.66366331376857) AND (long >=3D -78.70723462816063) AND (long <=3D > -78.53096764204116)) > Filter: (boxtype =3D 'd'::bpchar) > -> Index Scan using davic_pkey on davic (cost=3D0.00..6.01 rows=3D1 > width=3D8) (actual time=3D1.476..1.480 rows=3D1 loops=3D3224) > Index Cond: ("outer".mac =3D davic.mac) > Total runtime: 5100.028 ms > (7 rows) >=20 >=20 >=20 > twc-ral-overview=3D# vacuum analyze; > VACUUM > twc-ral-overview=3D# explain analyze SELECT lat, long, mac, status FROM= =20 > (SELECT text(mac) as mac, lat, long, CASE status WHEN 0 THEN 0 WHEN 1=20 > THEN 1 ELSE -1 END as status FROM cable_billing LEFT OUTER JOIN davic=20 > USING(mac) WHERE boxtype=3D'd') AS foo WHERE (long>=3D-78.87878592206046= )=20 > AND > (long<=3D-78.70220280717479) AND (lat>=3D35.71703190638861) AND > (lat<=3D35.80658335998006); > QUERY PLAN #2 >=20 > ---------------------------------------------------------------------- > ------ > ---------------------------------------------------------------------------- > ------------------- > Nested Loop Left Join (cost=3D0.00..76468.90 rows=3D9223 width=3D34) (a= ctual > time=3D0.559..17387.427 rows=3D19997 loops=3D1) > -> Seq Scan on cable_billing (cost=3D0.00..20837.76 rows=3D9223 widt= h=3D32) > (actual time=3D0.290..7117.799 rows=3D19997 loops=3D1) > Filter: ((boxtype =3D 'd'::bpchar) AND (long >=3D -78.8787859220= 6046) > AND (long <=3D -78.70220280717479) AND (lat >=3D 35.71703190638861) AND (= lat <=3D > 35.80658335998006)) > -> Index Scan using davic_pkey on davic (cost=3D0.00..6.01 rows=3D1 > width=3D8) (actual time=3D0.455..0.461 rows=3D1 loops=3D19997) > Index Cond: ("outer".mac =3D davic.mac) > Total runtime: 17416.501 ms > (6 rows) >=20 >=20 >=20 > twc-ral-overview=3D# explain analyze SELECT lat, long, mac, status FROM= =20 > (SELECT text(mac) as mac, lat, long, CASE status WHEN 0 THEN 0 WHEN 1=20 > THEN 1 ELSE -1 END as status FROM cable_billing LEFT OUTER JOIN davic=20 > USING(mac) WHERE boxtype=3D'd') AS foo WHERE (long>=3D-78.83419423836857= )=20 > AND > (long<=3D-78.7467945148866) AND (lat>=3D35.73964586635293) AND > (lat<=3D35.783969313080604); > QUERY PLAN #3 >=20 > ---------------------------------------------------------------------- > ------ > ---------------------------------------------------------------------------- > ----- > Nested Loop Left Join (cost=3D0.00..29160.02 rows=3D2327 width=3D34) (a= ctual > time=3D0.279..510.773 rows=3D5935 loops=3D1) > -> Index Scan using cable_billing_lat_long_idx on cable_billing > (cost=3D0.00..15130.08 rows=3D2326 width=3D32) (actual time=3D0.197..274.= 115 > rows=3D5935 loops=3D1) > Index Cond: ((lat >=3D 35.73964586635293) AND (lat <=3D > 35.783969313080604) AND (long >=3D -78.83419423836857) AND (long <=3D > -78.7467945148866)) > Filter: (boxtype =3D 'd'::bpchar) > -> Index Scan using davic_pkey on davic (cost=3D0.00..6.01 rows=3D1 > width=3D8) (actual time=3D0.021..0.024 rows=3D1 loops=3D5935) > Index Cond: ("outer".mac =3D davic.mac) > Total runtime: 516.782 ms > (7 rows) >=20 > ---------------------------------------------------------------------- > ------ > ----------------------------------- >=20 > iglass Networks > 211-A S. Salem St. > (919) 387-3550 x813 > P.O. Box 651 > (919) 387-3570 fax > Apex, NC 27502 > http://www.iglass.net >=20 >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? >=20 > http://www.postgresql.org/docs/faqs/FAQ.html >=20 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly