From: Anton <anton200@gmail.com>
To: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Subject: Re: JOIN work somehow strange on simple query
Date: Tue, 5 Dec 2006 14:38:54 +0500
Message-ID: <8cac8dd0612050138t4920d8a8tb2de52da64e8abd9@mail.gmail.com> (raw)
In-Reply-To: <8cac8dd0612050105h2273006cx8e429c5baa800937@mail.gmail.com>
References: <8cac8dd0612050105h2273006cx8e429c5baa800937@mail.gmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: anton200@gmail.com, pgsql-general@postgresql.org
Subject: Re: JOIN work somehow strange on simple query
In-Reply-To: <8cac8dd0612050138t4920d8a8tb2de52da64e8abd9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox