X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A04209FA2C7 for ; Mon, 29 May 2006 12:40:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 22433-05 for ; Mon, 29 May 2006 12:39:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id D164C9FA16E for ; Mon, 29 May 2006 12:39:52 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id o67so85641pye for ; Mon, 29 May 2006 08:39:51 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=cgHG/4IVXgsDws4xtyWOh8805GK6zvGcdzZefWbRBQCo0YNEqI7HogZWpeJXyJOcCtnbLwO9JCT7bMZQQQtkYMfP4OosYA/QgmlwWin5FLnZ580OH9pn33QMdF9uUehUtpPZCbYmLWjMRYQYIbCt11o7n4EFeiy8pulOSFHf4xs= Received: by 10.35.91.15 with SMTP id t15mr977337pyl; Mon, 29 May 2006 08:39:51 -0700 (PDT) Received: by 10.35.89.9 with HTTP; Mon, 29 May 2006 08:39:51 -0700 (PDT) Message-ID: <8cac8dd0605290839j1f1aa30bj124d617012da0009@mail.gmail.com> Date: Mon, 29 May 2006 21:39:51 +0600 From: "Anton Maksimenkov" To: "chris smith" Subject: Re: select with "like" from another table Cc: pgsql-performance@postgresql.org In-Reply-To: <3c1395330605290336q5c894215ge28eea597626c781@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <8cac8dd0605290253v75d43b9p249ebf58ee28b63c@mail.gmail.com> <3c1395330605290336q5c894215ge28eea597626c781@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/517 X-Sequence-Number: 19304 > > I have 2 tables - one with calls numbers and another with calls codes. > > The structure almost like this: > ... > How long does this query take? > > SELECT code FROM a_voip_codes c, a_voip v where v.called_station_id > like c.code || > '%' order by code desc limit 1 billing=3D# explain analyze SELECT code FROM a_voip_codes c, a_voip v where v.called_station_id like c.code || '%' order by code desc limit 1; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----- Limit (cost=3D0.00..11.24 rows=3D1 width=3D4) (actual time=3D15809.846..15809.848 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..35877212.61 rows=3D3192650 width=3D4) (actual time=3D15809.841..15809.841 rows=3D1 loops=3D1) Join Filter: (("inner".called_station_id)::text ~~ (("outer".code)::text || '%'::text)) -> Index Scan Backward using a_voip_codes_pkey on a_voip_codes c (cost=3D0.00..69.87 rows=3D2078 width=3D4) (actual time=3D0.029..0.106 rows=3D6 loops=3D1) -> Seq Scan on a_voip v (cost=3D0.00..11887.81 rows=3D307281 width=3D13) (actual time=3D1.696..935.368 rows=3D254472 loops=3D6) Total runtime: 15810.088 ms (6 rows) > I wonder if you'll benefit from an index on a_voip(called_station_id) > to speed up this join. Yes, it's long. But index gives no help here: billing=3D# CREATE INDEX a_voip_called_station_id ON a_voip(called_station_= id); CREATE INDEX billing=3D# vacuum analyze; VACUUM billing=3D# explain analyze SELECT code FROM a_voip_codes c, a_voip v where v.called_station_id like c.code || '%' order by code desc limit 1; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----- Limit (cost=3D0.00..11.27 rows=3D1 width=3D4) (actual time=3D15254.783..15254.785 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..35767665.65 rows=3D3172732 width=3D4) (actual time=3D15254.778..15254.778 rows=3D1 loops=3D1) Join Filter: (("inner".called_station_id)::text ~~ (("outer".code)::text || '%'::text)) -> Index Scan Backward using a_voip_codes_pkey on a_voip_codes c (cost=3D0.00..69.87 rows=3D2078 width=3D4) (actual time=3D0.021..0.097 rows=3D6 loops=3D1) -> Seq Scan on a_voip v (cost=3D0.00..11868.64 rows=3D305364 width=3D13) (actual time=3D0.006..750.337 rows=3D254472 loops=3D6) Total runtime: 15255.066 ms (6 rows) The main problem with first (main) query: SELECT user_name, called_station_id, (SELECT code FROM a_voip_codes AS c where v.called_station_id LIKE c.code || '%' order by code desc limit 1) AS code FROM a_voip AS v WHERE user_name =3D 'dixi' AND tm between '2006-04-01' and '2006-05-01' group by user_name, called_station_id; is that internal (SELECT... v.called_station_id LIKE c.code || '%'...) executed for each row, returned by external SELECT user_name... part. So I looking how to avoid internal (SELECT ...) part of query. Terrible oracle gives something like "over by (partition by ... order by code desc) rnum ... where rnum =3D 1" which works like DISTINCT and numerate similate rows, then we get just longest (rnum =3D 1) rows. But I can't imagine how to implement some kind of this algorithm with postgres. --=20 engineer