Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gXrn6-000131-DZ for pgsql-admin@arkaria.postgresql.org; Fri, 14 Dec 2018 18:02:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gXrn4-0001WD-UF for pgsql-admin@arkaria.postgresql.org; Fri, 14 Dec 2018 18:02:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gXrn4-0001W6-L9 for pgsql-admin@lists.postgresql.org; Fri, 14 Dec 2018 18:02:22 +0000 Received: from mout01.posteo.de ([185.67.36.65]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gXrn2-0001ea-6N for pgsql-admin@lists.postgresql.org; Fri, 14 Dec 2018 18:02:22 +0000 Received: from submission (posteo.de [89.146.220.130]) by mout01.posteo.de (Postfix) with ESMTPS id 9718F160060 for ; Fri, 14 Dec 2018 19:02:16 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=posteo.de; s=2017; t=1544810536; bh=NQJWjAJfVeIz2Fo7wXKlfp+OkaS3bBN0sghm9HkcbLE=; h=Subject:To:Cc:From:Openpgp:Autocrypt:Date:From; b=cfsZ/1FbTy1vp0uFd5xoWvBbai7qqZyeHN2f/SqyAS7tSnmb7wbyxTTYJqQPSlroI AzQtbNtjqcT1IGQdhIERkMj7R9jWQ0VFVq0i+rhkgYLTYzZPMN99XNAXjmsByodIy+ 3AcUwdzMtiQCdZY5JC4hkCSA8jzdGhFyxk9pXoXNSB8rc2Pyfj0ItekBgIstTbfj/r cfoCwQJCnsb2KMAju5OJfZ5azj2wLb17dVxQvTdASixoISVuvfettx6K6Yod1MIOU3 ESGwr1mcsr9qh5H8JQHMYBmT8NfnT92UORZgduqf82O03zOshs26bQH86dIXAB2YMU BtUjaF9sEW9sg== Received: from customer (localhost [127.0.0.1]) by submission (posteo.de) with ESMTPSA id 43Gdfv5VmTz6tm9; Fri, 14 Dec 2018 19:02:15 +0100 (CET) Subject: Re: finding queries doing sequential search To: Phil Frost Cc: pgsql-admin@lists.postgresql.org References: <6dd2fe53-a93d-a2ca-05aa-325921333dac@posteo.de> From: wambacher@posteo.de Openpgp: preference=signencrypt Autocrypt: addr=wambacher@posteo.de; keydata= mQINBFsF9hIBEAC4W3M068RHHa+QJ3GjfG8n+sSUenUmRP0YCBL0X9FZfMRk328cXXs2qFP4 yqoegbbRmxC4jUHCrMaPdnRGugW8ZRnyoiKYZrmffx/SzB6kZzdS7RCIhYY3pHLUEd5TIFPG Z2Plr6s1TpwVZc4Gkk9Uj4vGcwzDlUB7feurBpMdKaOfYz53vNXnh0C/e/fg7jx8rw70zM7z UDKcttHshsUUscL4tnYqH4mt2svJ8jmCwpesehjxgywTjxF8vPqr3G2KQ8cW2HbISp1q8gZQ vbXycBZ8Ej5GKsKkYLUP4bN6wfPE1sWlmD3Y5xilYNsDJkREFTQOqpJCoeMROcLZSdpv058Y nKRVVPKaD969nRl8lnMgPEkDOSv5V+IhSebQEFX7vnSkZp/4kETy4NsAa89I6ATBt4H5McQL 3Py46INBbn7cH3k4Vljf8R1qvWYltM9aLZp3HbmKyRl0c5DSRSzKm7jCspmaYQ6pxZfLAx9M HRp2m3tIZPF+o8r8LukVS4Ji/8pXu2Tubd1rpkWwa8P6DxE0ZZGXYEnn7iYQuBn1sOYU2PBG xo/EA2lYx/EfeMDvjFV0ZB8DafROff4pb1mvuTA8m8gyUwa2leCNFg17PqYI/k4zCsGO21Vg qKc2K2rzm9azfX6nPvfcQHZtK55ek9m/0cP0pCsi/3v75vypewARAQABtCtXYWx0ZXIgTm9y ZG1hbm4gPHdhbHRlci5ub3JkbWFubkBwb3N0ZW8uZGU+iQI9BBMBCAAnBQJbBfYSAhsjBQkJ ZgGABQsJCAcCBhUICQoLAgQWAgMBAh4BAheAAAoJENd8FQK2Z0Eya2oQAKp/5yxEPPyflX2o dLoPMdPPvZoTDG/PMGbLYqJ8jRPnIcopFCCu07LZDwS9rDjQEnMaDagdvxSHgVprdiJ8BUum 9UK5WDNj++a1tY20DwjVDNOBpZisF3W4GZ+89JIU9710HxcGr0JC2UV99I4IbPbanm5PLAgT csyeDWT5e8EXEPS/BF5adQQH86SsTwKEjQtxtzoI9bNWfEbWvS5ZE0ATGQYtjAilQl/QuFN8 LjdDkAUVTbQbR02I1r9jjpYWv0qCa043FifP0CSosKqOyPbTibvV+FYlMj2c9dj955MJ/BJB QvgEXmNRTN4vsZJEm9u+onJ0NAR2sNPvezIwuOX4mQcUsiDYQAit+MicjS9dJLeMnvcWFuBy boPdNopV2PjtkCzaLOD0jQ01drIBKLIkTGElPSl+EwhwX5PH+XzXY/f8KHIyGsJ9Fi9JZJDS qbFuYNLmIwWqdtvMECF5Cwp1g6UZeFX70ALo82sc/EYfnpfDnLUfTeayWkNDp9xsZMOuLk5M O+s+8tH1tJcIc3YEtVg8M+QZYnweTDoOZOgxsJuePvtMnQl1T5gXHA1W5YT6lrpiLaN2BZfO vjLyb9I+6hMYNtsqnGVP7gADLWIxqG5D8PZ1WYqsDTCzwKHZuasUnc+agOYbUKMo6DSo8g/I zMbF/dkpxD/6UPwRfMltuQINBFsF9hIBEAD038KpaE8OkPi9/dMaEewlz95rbDvSaUSxCm3w vNnrDRTqiophHoxXYjrrpeS9YPlkjscVYWRSw2Qqf2QYcFexv+8bGjZO1tYTDgLkdfPXrnO+ 2i9xy4dGLHEZRIcyZsS7J46fg9e1Ir6U8nFdAjXW7d/QBWh09QujZ0WXnajuQBeWfDNfKD6V 4+xV+xLndgvQ5elddSqjz6pV4/TcEtjgNg7dB/2cA93sUbikPyHG6jUSncYsa9I4zU94+CNh jUl0WUdcZl9+hRkzzkbxqWD9lxtsWjweN2bcKKpZ3tEVs3mALJauQc2FZMlKCF9fNuJwN9wx Ub1+uI/jBpi1OuuCNfHm/ChpLiiDVRPp/e/t8rNHcagN3hjrP/vBtzqFD6DFWxwZnIQEtoc/ P4K/RythMp07Xdfz2qjSXS2VGVmxgzGStjg6BlGXM3QDqgQt0heZqMWEj5+F7NVipN8ZFxmg rNoViVzqf24k5qwK3EQAUdXKj6UxIocAgJsZ6iE+QDKfd1OvXpRehpnq6/PSji5OZJ0S/QHB +NOs2BU7glMBcePgfagqxAWeD4ZZtt2iONyrt15ukAO/kmNZDdMBmz/6Fq75tqXRROl4CYuA y3ImcQ4r1rFuZTNmydVUM3uV8hOS2j5o3HjsHa91wyeCw4Ms2VkZlxfi4izFP/Yx9gWmFQAR AQABiQIlBBgBCAAPBQJbBfYSAhsMBQkJZgGAAAoJENd8FQK2Z0Ey3egP/jsLqXgKDTGOetw6 zOx+Y2o1XaL1/ov2yx5oSK9d5OagcSpby31eZbd17BkTz2ZV2S7RUoBN7F0JWfs41YkHkvF9 2+ANexNMXnTNrFBemxP2GB7C2MGFVwOhbYj8OhygLIukEtYbSktNBe+CPD76UynvPh2O8aGn f7IibXMntgOc/qm3olXNknZfcXc1hEfCDK2Kce404f7J6verPUm5/LyTVEPGbH4VQBiuwwxq vbvSJdv1pfzHihcjVVjNN/KVU0Um6+GqUj5JrI3JpW7waRERctwmOVc/hYGzGpbN8iUE0Oik Adanx2OanoyxcMBjg5LVgltnRVqVB6AEXRLYUDl7dvRl+68BmvQB08cnmg/UY5tFfxmSTlN/ /gqhThYXF4/3WH8rOtWZ2Gq75YdrMTJO3npJLoDhVCZZkxekcTIveQsv37b2nLZrvqrjWRl4 NAJD2K4kFICPEuErjLEsPIL4YBH2WNFlYpLgAjCBoShRV0IBd3dwF6BJnesR12Xh5H47qS9m +6h28JtTek9FvHCgmNiYcum8yBwBRwfSqLC4LJ9QU4JWb+rx6JLHkVu3sz274bv80B79gibD cjwz8joa6ISPfAaO2sJNhrKl23F8qor37FcXqauPnJwzS5cKNXcEpkBL9+OyPHBU0rMN4k+7 Xs1SWQ51ouO7nAA/IFGj Message-ID: <76b3b179-c19d-49c6-d0b9-72d63f3fa375@posteo.de> Date: Fri, 14 Dec 2018 19:02:15 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------9CB838A028174AE1737EA619" Content-Language: de-DE List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------9CB838A028174AE1737EA619 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Am 14.12.18 um 18:39 schrieb Phil Frost: > I look for queries with a high=C2=A0shared_blks_hit and/or=C2=A0shared_= blks_read > in pg_stat_statements. Although not directly indicative of sequential > scans, the correlation is usually pretty good. > > Also I suggest you look in pg_stat_user_tables and ensure that > seq_tup_read is high as well as seq_scan. For example this query: > > select * from a_really_big_table limit 1; > > will increment seq_scan, though since it scans only one tuple there's > not actually any problem here. Dividing seq_tup_read by seq_scan will > yield the mean number of tuples read per sequential scan, and if > that's a small number it could be that the sequential scans you are > seeing are in fact a non-issue. 3.6 mio tup is quite much, i think: select seq_tup_read,seq_scan, seq_tup_read/seq_scan "tup per scan" from pg_stat_user_tables where relname =3D 'planet_osm_polygon'; =C2=A0seq_tup_read | seq_scan | tup per scan --------------+----------+-------------- =C2=A0=C2=A0 1517870594 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 426 |=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 3563076 (1 Zeile) @tom: I'll try auto_explain next. --=20 My projects: Admin Boundaries of the World = Missing Boundaries Emergency Map Postal Code Map (Germany only) Fools (QA for zipcodes in Germany) = Postcode Boundaries of Germany --------------9CB838A028174AE1737EA619 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit


Am 14.12.18 um 18:39 schrieb Phil Frost:
I look for queries with a high shared_blks_hit and/or shared_blks_read in pg_stat_statements. Although not directly indicative of sequential scans, the correlation is usually pretty good.

Also I suggest you look in pg_stat_user_tables and ensure that seq_tup_read is high as well as seq_scan. For example this query:

select * from a_really_big_table limit 1;

will increment seq_scan, though since it scans only one tuple there's not actually any problem here. Dividing seq_tup_read by seq_scan will yield the mean number of tuples read per sequential scan, and if that's a small number it could be that the sequential scans you are seeing are in fact a non-issue.

3.6 mio tup is quite much, i think:

select seq_tup_read,seq_scan, seq_tup_read/seq_scan "tup per scan" from pg_stat_user_tables
where relname = 'planet_osm_polygon';
 seq_tup_read | seq_scan | tup per scan
--------------+----------+--------------
   1517870594 |      426 |      3563076
(1 Zeile)

@tom: I'll try auto_explain next.

--
My projects:

Admin Boundaries of the World
Missing Boundaries
Emergency Map
Postal Code Map (Germany only)
Fools (QA for zipcodes in Germany)
Postcode Boundaries of Germany
--------------9CB838A028174AE1737EA619--