Andrey,
- Another idea for your problem is the one Kevin gave in the message following:
##########################################################################################################################
> SELECT * FROM t_route
> WHERE t_route.route_type_fk = 1
> limit 4;
This one scanned the t_route table until it found four rows that
matched. It apparently didn't need to look at very many rows to find
the four matches, so it was fast.
> SELECT * FROM t_route
> WHERE t_route.route_type_fk =
> (SELECT id FROM t_route_type WHERE type = 2)
> limit 4;
This one came up with an id for a route type that didn't have any
matches in the t_route table, so it had to scan the entire t_route
table. (Based on your next query, the subquery probably returned
NULL, so there might be room for some optimization here.) If you had
chosen a route type with at least four matches near the start of the
route table, this query would have completed quickly.
> SELECT * FROM t_route, t_route_type
> WHERE t_route.route_type_fk = t_route_type.id
> AND type = 2
> limit 4;
Since it didn't find any t_route_type row which matched, it knew
there couldn't be any output from the JOIN, so it skipped the scan of
the t_route table entirely.
-Kevin
##############################################################################################################
Regards....
-------- Original Message --------
From: - Fri Apr 9 17:36:41 2010
X-Account-Key: account3
X-UIDL: GmailId127e449663a13d39
X-Mozilla-Status: 0011
X-Mozilla-Status2: 00000000
X-Mozilla-Keys:
Delivered-To: helio.cam...@gmail.com
Received: by 10.231.79.67 with SMTP id o3cs40933ibk; Fri, 9 Apr 2010
13:36:16 -0700 (PDT)
Received: by 10.114.248.22 with SMTP id v22mr967398wah.8.1270845368202;
Fri, 09 Apr 2010 13:36:08 -0700 (PDT)
Return-Path: <pgsql-performance-owner+m38...@postgresql.org>
Received: from maia-1.hub.org (maia-1.hub.org [200.46.208.211]) by
mx.google.com with ESMTP id 8si1947813ywh.11.2010.04.09.13.36.07; Fri,
09 Apr 2010 13:36:08 -0700 (PDT)
Received-SPF: neutral (google.com: 200.46.208.211 is neither permitted
nor denied by best guess record for domain of
pgsql-performance-owner+m38...@postgresql.org) client-ip=200.46.208.211;
Authentication-Results: mx.google.com; spf=neutral (google.com:
200.46.208.211 is neither permitted nor denied by best guess record for
domain of pgsql-performance-owner+m38...@postgresql.org)
smtp.mail=pgsql-performance-owner+m38...@postgresql.org
Received: from postgresql.org (mail.postgresql.org [200.46.204.86]) by
maia-1.hub.org (Postfix) with ESMTP id 54BAEAFD1B6; Fri, 9 Apr 2010
20:36:00 +0000 (UTC)
Received: from maia.hub.org (unknown [200.46.204.183]) by
mail.postgresql.org (Postfix) with ESMTP id 2E74B633047 for
<pgsql-performance-postgresql....@mail.postgresql.org>; Thu, 8 Apr 2010
22:36:17 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org
(mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id
90832-06 for <pgsql-performance-postgresql....@mail.postgresql.org>;
Fri, 9 Apr 2010 01:36:06 +0000 (UTC)
Received: from news.hub.org (news.hub.org [200.46.204.72]) by
mail.postgresql.org (Postfix) with ESMTP id BBD50632DC3 for
<pgsql-performance@postgresql.org>; Thu, 8 Apr 2010 22:36:06 -0300 (ADT)
Received: from news.hub.org (news.hub.org [200.46.204.72]) by
news.hub.org (8.14.3/8.14.3) with ESMTP id o391a091050073 for
<pgsql-performance@postgresql.org>; Thu, 8 Apr 2010 22:36:00 -0300 (ADT)
(envelope-from n...@news.hub.org)
Received: (from n...@localhost) by news.hub.org (8.14.3/8.14.3/Submit)
id o391DTvp041710 for pgsql-performance@postgresql.org; Thu, 8 Apr 2010
22:13:29 -0300 (ADT) (envelope-from news)
From: norn <andrey.perl...@gmail.com>
X-Newsgroups: pgsql.performance
Subject: Re: [PERFORM] significant slow down with various LIMIT
Date: Thu, 8 Apr 2010 18:13:33 -0700 (PDT)
Organization: http://groups.google.com
Lines: 72
Message-ID:
<8ae12099-1cbb-40d5-b7fc-c15b8deba...@30g2000yqi.googlegroups.com>
References:
<9587baca-c902-4215-9863-7043802ec...@10g2000yqq.googlegroups.com>
<4bbdc19a0200002500030...@gw.wicourts.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Complaints-To: groups-ab...@google.com
Complaints-To: groups-ab...@google.com
Injection-Info: 30g2000yqi.googlegroups.com;
posting-host=94.78.201.171;
posting-account=woDzKwoAAACEqYut1Qq-BHNhLOB-6ihP
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux x86_64; en-US)
AppleWebKit/533.4 (KHTML, like Gecko) Chrome/5.0.368.0
Safari/533.4,gzip(gfe)
To: pgsql-performance@postgresql.org
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.74 tagged_above=-10 required=5
tests=BAYES_20=-0.74
X-Spam-Level:
X-Mailing-List: pgsql-performance
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majord...@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-ow...@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe:
<mailto:majord...@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe:
<mailto:majord...@postgresql.org?body=unsub%20pgsql-performance>
Precedence: bulk
Sender: pgsql-performance-ow...@postgresql.org
Kevin, thanks for your attention!
I've read SlowQueryQuestions, but anyway can't find bottleneck...
Here requested information:
OS: Ubuntu 9.10 64bit, Postgresql 8.4.2 with Postgis
Hardware: AMD Phenom(tm) II X4 945, 8GB RAM, 2 SATA 750GB (pg db
installed in software RAID 0)
Please also note that this hardware isn't dedicated DB server, but
also serve as web server and file server.
I have about 3 million rows in core_object, 1.5 million in
plugin_plugin_addr and 1.5 million in plugins_guide_address.
When there were 300 000+ objects queries works perfectly, but as db
enlarge things go worse...
# select version();
PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.1-4ubuntu8) 4.4.1, 64-bit
---postgresql.conf---
data_directory = '/mnt/fast/postgresql/8.4/main'
hba_file = '/etc/postgresql/8.4/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.4/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/8.4-main.pid'
listen_addresses = 'localhost'
port = 5432
max_connections = 250
unix_socket_directory = '/var/run/postgresql'
ssl = true
shared_buffers = 1024MB
temp_buffers = 16MB
work_mem = 128MB
maintenance_work_mem = 512MB
fsync = off
wal_buffers = 4MB
checkpoint_segments = 16
effective_cache_size = 1536MB
log_min_duration_statement = 8000
log_line_prefix = '%t '
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
standard_conforming_strings = on
escape_string_warning = off
constraint_exclusion = on
checkpoint_completion_target = 0.9
---end postgresql.conf---
I hope this help!
Any ideas are appreciated!
On Apr 9, 12:44 am, kevin.gritt...@wicourts.gov ("Kevin Grittner")
wrote:
Could you show us the output from "select version();", describe your
hardware and OS, and show us the contents of your postgresql.conf
file (with all comments removed)? We can then give more concrete
advice than is possible with the information provided so far.
http://wiki.postgresql.org/wiki/SlowQueryQuestions
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org)
To make changes to your
subscription:http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
