Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9CF6B63333A for ; Mon, 12 Apr 2010 07:23:42 -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 25141-07 for ; Mon, 12 Apr 2010 10:23:29 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 6390E6325ED for ; Mon, 12 Apr 2010 07:23:29 -0300 (ADT) Received: by vws18 with SMTP id 18so1023772vws.19 for ; Mon, 12 Apr 2010 03:23:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:content-type; bh=fJgfntj7bM2TQsaV+ft111VQ9OMfJywLc2xgMty6Ufg=; b=x/0fk3unZZ8GZ/u1VVyVEIjlJymsoZ2jrmYhkT7KOYoK8vGpA7hYRI8jqQgCEsdHbu Sjj5wIvzvY2A61KmTF+jW0aK41AuYKTVtBj2OUWX6kR2jAkKY8z5TvIsmTmo353FwKPM 8Fa7tqMAW36vHYcGxPTS/BAQyqT0eQoyHHgcU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; b=VtO9uZgh40hCnjIBTpYFLWbGNj4YWPF6AMTYr/1Tlpd1H4HQb1qHZT1POmBAZziZps PrmlB+pqBRjAxs94LcDNoLF+/6AVCL5//XfHtWC3/uoeWcMha02k5NRQ4FI4pPTiDOT0 /4Sh86+EYY8c7DmNc55mnM1ej0wv3bzNgyntY= Received: by 10.220.107.71 with SMTP id a7mr1863689vcp.111.1271067808046; Mon, 12 Apr 2010 03:23:28 -0700 (PDT) Received: from [192.168.0.194] (c93479ce.virtua.com.br [201.52.121.206]) by mx.google.com with ESMTPS id 22sm96881778vws.3.2010.04.12.03.23.24 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 12 Apr 2010 03:23:26 -0700 (PDT) Message-ID: <4bc2f49e.9615f10a.46f6.ffffd71b@mx.google.com> Date: Mon, 12 Apr 2010 07:23:20 -0300 From: Helio Campos Mello de Andrade User-Agent: Thunderbird 2.0.0.24 (X11/20100317) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: significant slow down with various LIMIT Content-Type: multipart/alternative; boundary="------------000007050201020909010105" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.012 tagged_above=-10 required=5 tests=FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201004/148 X-Sequence-Number: 38398 This is a multi-part message in MIME format. --------------000007050201020909010105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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.campos@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: 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+M38376@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+M38376@postgresql.org) smtp.mail=pgsql-performance-owner+M38376@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 ; 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 ; 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 ; 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 ; Thu, 8 Apr 2010 22:36:00 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@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 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-c15b8deba021@30g2000yqi.googlegroups.com> References: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com> <4BBDC19A02000025000305A4@gw.wicourts.gov> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Complaints-To: groups-abuse@google.com Complaints-To: groups-abuse@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: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: Precedence: bulk Sender: pgsql-performance-owner@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 --------------000007050201020909010105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 
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.campos@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+M38376@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+M38376@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+M38376@postgresql.org) smtp.mail=pgsql-performance-owner+M38376@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.org@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.org@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 news@news.hub.org)
Received: (from news@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.perliev@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-c15b8deba021@30g2000yqi.googlegroups.com>
References: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com> <4BBDC19A02000025000305A4@gw.wicourts.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Complaints-To: groups-abuse@google.com
Complaints-To: groups-abuse@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:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Precedence: bulk
Sender: pgsql-performance-owner@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
--------------000007050201020909010105--