Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id CCB879F9F49 for ; Tue, 5 Dec 2006 05:39:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 08261-06 for ; Tue, 5 Dec 2006 05:38:57 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 2510A9F9398 for ; Tue, 5 Dec 2006 05:38:56 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so168557nfb for ; Tue, 05 Dec 2006 01:38:55 -0800 (PST) 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=ZD4uWG3wiagfgmnwtWSEMaeArM2D1WTAoZ2K79yekvjOG0hJrO6SL7ymOxZFwDgkUoD3YC6LuUHVCmkaoF7LQeM7SQMkj7i5/z/B9IAuaC7S5vBT+RNnks8BM83nA6VGko+3M/gUfrZZUplVP1U9y/35nai443kkpVDC7utJ5Ro= Received: by 10.82.105.13 with SMTP id d13mr1680288buc.1165311534982; Tue, 05 Dec 2006 01:38:54 -0800 (PST) Received: by 10.82.167.6 with HTTP; Tue, 5 Dec 2006 01:38:54 -0800 (PST) Message-ID: <8cac8dd0612050138t4920d8a8tb2de52da64e8abd9@mail.gmail.com> Date: Tue, 5 Dec 2006 14:38:54 +0500 From: Anton To: pgsql-performance@postgresql.org Subject: Re: JOIN work somehow strange on simple query Cc: pgsql-general@postgresql.org In-Reply-To: <8cac8dd0612050105h2273006cx8e429c5baa800937@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <8cac8dd0612050105h2273006cx8e429c5baa800937@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/170 X-Sequence-Number: 105109 > Hi. I have a performance problem with this simple query: > > SELECT collect_time FROM n_traffic JOIN n_logins USING (login_id) > WHERE n_logins.account_id = '1655' ORDER BY collect_time LIMIT 1; I must add that is occurs when there is no rows in n_traffic for these login_id's. Where there is at least one (example, login_id='411' belongs to account_id='1655') query goes fast: =# INSERT INTO n_traffic VALUES ('411', '1', '2006-09-23 12:23:05', '0', '0'); =# explain analyze SELECT collect_time FROM n_traffic JOIN n_logins USING (login_id) WHERE n_logins.account_id= '1655' ORDER BY collect_time LIMIT 1; ------------------------------------ Limit (cost=0.00..2025.76 rows=1 width=8) (actual time=0.070..0.072 rows=1 loops=1) -> Nested Loop (cost=0.00..913617.15 rows=451 width=8) (actual time=0.065..0.065 rows=1 loops=1) -> Index Scan using n_traffic_collect_time_login_id on n_traffic (cost=0.00..10418.19 rows=285290 width=12) (actual time=0.026..0.026 rows=1 loops=1) -> Index Scan using n_logins_pkey on n_logins (cost=0.00..3.15 rows=1 width=4) (actual time=0.026..0.026 rows=1 loops=1) Index Cond: ("outer".login_id = n_logins.login_id) Filter: (account_id = 1655) Total runtime: 0.322 ms (7 rows) -- engineer