Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VOOrE-0007wo-S4 for pgsql-performance@arkaria.postgresql.org; Tue, 24 Sep 2013 09:24:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VOOrE-0001qy-C9 for pgsql-performance@arkaria.postgresql.org; Tue, 24 Sep 2013 09:24:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VOOrD-0001qa-5r for pgsql-performance@postgresql.org; Tue, 24 Sep 2013 09:24:35 +0000 Received: from [202.181.174.115] (helo=doravmmail.chowchi.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VOOqz-0004AL-U0 for pgsql-performance@postgresql.org; Tue, 24 Sep 2013 09:24:32 +0000 Received: from longwt3 (123202017137.ctinets.com [123.202.17.137]) by doravmmail.chowchi.com (Postfix) with ESMTPA id 1220B340B93 for ; Tue, 24 Sep 2013 17:24:13 +0800 (HKT) From: "Sam Wong" To: Subject: Slow plan for MAX/MIN or LIMIT 1? Date: Tue, 24 Sep 2013 17:24:07 +0800 Message-ID: <007801ceb907$d3706070$7a512150$@hellosam.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0079_01CEB94A.E1965F90" X-Mailer: Microsoft Outlook 14.0 thread-index: Ac64y+WGScf0mgM/Tcya6qW+OZKHlQ== Content-Language: en-us X-Host-Lookup-Failed: Reverse DNS lookup failed for 202.181.174.115 (failed) X-Pg-Spam-Score: 0.3 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0079_01CEB94A.E1965F90 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi There, I have hit a query plan issue that I believe is a bug or under-estimation, and would like to know if there it is known or if there is any workaround. This event_log table has 4 million rows. "log_id" is the primary key (bigint), there is a composite index "event_data_search" over (event::text, insert_time::datetime). Query A: SELECT min(log_id) FROM event_log WHERE event='S-Create' AND insert_time>'2013-09-15' and insert_time<'2013-09-16' Query B: SELECT log_id FROM event_log WHERE event='S-Create' AND insert_time>'2013-09-15' and insert_time<'2013-09-16' ORDER BY log_id What I want to achieve is Query A - get the min log_id within a range. But it is very slow, taking 10 or 20 seconds. If I don't limit the output to LIMIT 1 - like Query B - then it is sub-second fast. Explain of A - take 10~20 seconds to run Limit (cost=0.00..132.54 rows=1 width=8) -> Index Scan using event_log_pkey on event_log (cost=0.00..1503484.33 rows=11344 width=8) Filter: ((insert_time > '2013-09-15 00:00:00'::timestamp without time zone) AND (insert_time < '2013-09-16 00:00:00'::timestamp without time zone) AND (event = 'S-Create'::text)) Explain of B - take a few milliseconds to run Sort (cost=41015.85..41021.52 rows=11344 width=8) Sort Key: log_id -> Bitmap Heap Scan on event_log (cost=311.42..40863.05 rows=11344 width=8) Recheck Cond: ((event = 'S-Create'::text) AND (insert_time > '2013-09-15 00:00:00'::timestamp without time zone) AND (insert_time < '2013-09-16 00:00:00'::timestamp without time zone)) -> Bitmap Index Scan on event_data_search (cost=0.00..310.86 rows=11344 width=0) Index Cond: ((event = 'S-Create'::text) AND (insert_time > '2013-09-15 00:00:00'::timestamp without time zone) AND (insert_time < '2013-09-16 00:00:00'::timestamp without time zone)) Plan of A thought that the index scan node will get the first row right at 0.00, and hence the limit node will get all the rows needed within 132.54 (because event_log_pkey are sorted) I would like to point out that - this 0.00 estimation omits the fact that it actually takes a much longer time for the index scan node to get the first row, because 3.99M rows it comes across won't meet the condition filter at all. Other background info: The event_log table has been vacuumed and analyzed. I have PostgreSQL 9.2.4 (x64) on Windows Server 2008 R2 with me. 8GB ram. 1*Xeon E5606. Thanks, Sam ------=_NextPart_000_0079_01CEB94A.E1965F90 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi = There,

 

I have hit a query plan issue that I believe is a bug or = under-estimation, and would like to know if there it is known or if = there is any workaround…

 

This event_log table has 4 million = rows.

“log_id” is the primary key (bigint), =

there is a = composite index “event_data_search” over (event::text, = insert_time::datetime).

 

Query A:

SELECT min(log_id) FROM event_log

WHERE event=3D'S-Create' = AND

insert_time>'2013-09-15' and = insert_time<'2013-09-16'

 

Query B:

SELECT log_id FROM event_log =

WHERE = event=3D'S-Create' AND

insert_time>'2013-09-15' and = insert_time<'2013-09-16'

ORDER BY = log_id

 

What I want to achieve is Query A – get the min = log_id within a range. But it is very slow, taking 10 or 20 = seconds.

If = I don’t limit the output to LIMIT 1 – like Query B – = then it is sub-second fast.

 

Explain of A – take 10~20 = seconds to run

Limit  (cost=3D0.00..132.54 rows=3D1 = width=3D8)

  ->  Index Scan using event_log_pkey on = event_log  (cost=3D0.00..1503484.33 rows=3D11344 = width=3D8)

        Filter: = ((insert_time > '2013-09-15 00:00:00'::timestamp without time zone) = AND (insert_time < '2013-09-16 00:00:00'::timestamp without time = zone) AND (event =3D 'S-Create'::text))

 

Explain of B – take a few = milliseconds to run

Sort  (cost=3D41015.85..41021.52 rows=3D11344 = width=3D8)

  Sort Key: log_id

  ->  Bitmap Heap Scan = on event_log  (cost=3D311.42..40863.05 rows=3D11344 = width=3D8)

        Recheck Cond: = ((event =3D 'S-Create'::text) AND (insert_time > '2013-09-15 = 00:00:00'::timestamp without time zone) AND (insert_time < = '2013-09-16 00:00:00'::timestamp without time = zone))

        ->  = Bitmap Index Scan on event_data_search  (cost=3D0.00..310.86 = rows=3D11344 width=3D0)

          =     Index Cond: ((event =3D 'S-Create'::text) AND = (insert_time > '2013-09-15 00:00:00'::timestamp without time zone) = AND (insert_time < '2013-09-16 00:00:00'::timestamp without time = zone))

 

Plan of A thought that the index scan node will get the = first row right at 0.00, and hence the limit node will get all the rows = needed within 132.54 (because event_log_pkey are = sorted)

I = would like to point out that – this 0.00 estimation omits the fact = that it actually takes a much longer time for the index scan node to get = the first row, because 3.99M rows it comes across won’t meet the = condition filter at all.

 

Other background info:

The event_log table has been = vacuumed and analyzed.

I have PostgreSQL 9.2.4 (x64) on Windows Server 2008 R2 = with me. 8GB ram. 1*Xeon E5606.

 

Thanks,

Sam

------=_NextPart_000_0079_01CEB94A.E1965F90--