Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qw0hd-0092FA-1x for pgsql-performance@arkaria.postgresql.org; Thu, 26 Oct 2023 13:47:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qw0hb-000WyV-Jh for pgsql-performance@arkaria.postgresql.org; Thu, 26 Oct 2023 13:47:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qw0hb-000Wy2-2b for pgsql-performance@lists.postgresql.org; Thu, 26 Oct 2023 13:47:11 +0000 Received: from mail-lf1-x12b.google.com ([2a00:1450:4864:20::12b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qw0hX-002Pl2-48 for pgsql-performance@postgresql.org; Thu, 26 Oct 2023 13:47:09 +0000 Received: by mail-lf1-x12b.google.com with SMTP id 2adb3069b0e04-507ac66a969so1081565e87.3 for ; Thu, 26 Oct 2023 06:47:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=stiltsoft.com; s=google; t=1698328025; x=1698932825; darn=postgresql.org; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=1ISAG4K/NdrMXJQGeJpZCFIhVpaFAXl36Gb8KSkHfDk=; b=jozCKhCuSb3nRg/3TpnsnSlkqM8AFuFjPX5hl9bgWZhNYNvVksTSJ6yFPKmKElxgQh jF0+yLoAEfeRYdeyDlBWo+jFGGvTWqTpPsjLY7gXFbKN8tz0yhPoqabP5tbih/c1OXma IwFHgYjj5yjNtEB/fJtbt2rGymAXmkAckThn0= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1698328025; x=1698932825; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=1ISAG4K/NdrMXJQGeJpZCFIhVpaFAXl36Gb8KSkHfDk=; b=Jg/Y54aQyS7TPjlxHCtQRGLklYZ1M3ZoHDwRN5p3yXHRLmaq6DBnVyBAKZglCnRgA8 my/N8vO/xJRX+VO9Wns2FsBRVFHHTYqzJOUMSsNS5ard6KaI7ucqwf7gcur9vL3x6K6j /DJYcD9LyOpbD34irOaJdAn2VZv7MHpAhyrv23tJXLDxAh3XkFCAiEAIx8675+tLzXLA 76CBl5IsyYJ59uq2BmJKPB2jytCph45mRngcU59CU/+aAlA6Dc4jIYD+ms92VsNHYAK6 kzjmY0YOa9hYjL9QcucmE7IO8p3xXZ1cHu+JDT/9D9q6RIrTlBaMdyFZYzBI5CEgOYcm qd2Q== X-Gm-Message-State: AOJu0Yz/wiAv01mqO/6xC7yYp6Yobxz4ZHrm7HQxkIemEW4iMZlcEZ+K O6QbAyOlHhMYtwAADXO/bLnTE5wniZ+kP1kTn6dE9JAs X-Google-Smtp-Source: AGHT+IHaofi/gtnNv5I6MLzEgahDsXM1Gkx9biSA1+Mi8Pbwd18ScIsaXxwc0BbBayZpuybsb2AikQ== X-Received: by 2002:a05:6512:34c7:b0:500:ac71:8464 with SMTP id w7-20020a05651234c700b00500ac718464mr10765416lfr.66.1698328025171; Thu, 26 Oct 2023 06:47:05 -0700 (PDT) Received: from ?IPV6:2a02:a31d:8540:a280:38a:ec48:fa5a:d99e? ([2a02:a31d:8540:a280:38a:ec48:fa5a:d99e]) by smtp.gmail.com with ESMTPSA id s5-20020a197705000000b00504358a9423sm3058093lfc.280.2023.10.26.06.47.04 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 26 Oct 2023 06:47:04 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------d0olV6uQLZUsxnP6lapvuW5s" Message-ID: Date: Thu, 26 Oct 2023 15:47:03 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Postgres 15 SELECT query doesn't use index under RLS Content-Language: en-US To: Tom Lane Cc: pgsql-performance@postgresql.org References: <5c1179bb-240b-4c1c-b4b3-2a24868e44bc@stiltsoft.com> <1570249.1697228785@sss.pgh.pa.us> <3153246.1697661350@sss.pgh.pa.us> From: Alexander Okulovich In-Reply-To: <3153246.1697661350@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------d0olV6uQLZUsxnP6lapvuW5s Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi Tom, I've attempted to reproduce this on my PC in Docker from the stage database dump, but no luck. The first query execution on Postgres 15 behaves like on the real stage, but subsequent ones use the index. Also, they execute much faster. Looks like the hardware and(or) the data structure on disk matters. Here is the Docker Compose sample config: > version:'2.4' services: > database-15: > image: postgres:15.4 > ports: > -"7300:5432" environment: > POSTGRES_DB: stage_db > POSTGRES_USER: stage > POSTGRES_PASSWORD: stage > volumes: > -"./init.sql:/docker-entrypoint-initdb.d/init.sql" -"./pgdb/aws-15:/var/lib/postgresql/data" mem_limit: 512M > cpus: 2 > blkio_config: > device_read_bps: > -path: /dev/nvme0n1 > rate:'10mb' device_read_iops: > -path: /dev/nvme0n1 > rate: 2000 > device_write_bps: > -path: /dev/nvme0n1 > rate:'10mb' device_write_iops: > -path: /dev/nvme0n1 > rate: 2000 I performed tests only with CPU and memory limits. If I try to limit the disk(blkio_config), my system hangs on container startup after a while. Could you please share your thoughts on how to create such a self-contained test case. Kind regards, Alexander On 18.10.2023 22:35, Tom Lane wrote: > If you could provide a self-contained test case that performs 10x > worse under v15 than v12, we'd surely take a look at it. But with the > information you've given so far, little is possible beyond speculation. --------------d0olV6uQLZUsxnP6lapvuW5s Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

Hi Tom,

I've attempted to reproduce this on my PC in Docker from the stage database dump, but no luck. The first query execution on Postgres 15 behaves like on the real stage, but subsequent ones use the index. Also, they execute much faster. Looks like the hardware and(or) the data structure on disk matters.

Here is the Docker Compose sample config:

version: '2.4'

services:
database-15:
  image: postgres:15.4
  ports:
    - "7300:5432"
  environment:
    POSTGRES_DB: stage_db
    POSTGRES_USER: stage
    POSTGRES_PASSWORD: stage
  volumes:
    - "./init.sql:/docker-entrypoint-initdb.d/init.sql"
    - "./pgdb/aws-15:/var/lib/postgresql/data"
  mem_limit: 512M
  cpus: 2
  blkio_config:
    device_read_bps:
      - path: /dev/nvme0n1
        rate: '10mb'
    device_read_iops:
        - path: /dev/nvme0n1
          rate: 2000
    device_write_bps:
        - path: /dev/nvme0n1
          rate: '10mb'
    device_write_iops:
        - path: /dev/nvme0n1
          rate: 2000
I performed tests only with CPU and memory limits. If I try to limit the disk(blkio_config), my system hangs on container startup after a while.
Could you please share your thoughts on how to create such a self-contained test case.

Kind regards,

Alexander

On 18.10.2023 22:35, Tom Lane wrote:
If you could provide a self-contained test case that performs 10x worse under v15 than v12, we'd surely take a look at it. But with the information you've given so far, little is possible beyond speculation.
--------------d0olV6uQLZUsxnP6lapvuW5s--