pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange behavior of limit clause in complex query
4+ messages / 3 participants
[nested] [flat]
* Strange behavior of limit clause in complex query
@ 2022-06-08 08:44 Paulo Silva <paulojjs@gmail.com>
2022-06-08 11:40 ` Re: Strange behavior of limit clause in complex query Ranier Vilela <ranier.vf@gmail.com>
2022-06-08 14:32 ` Re: Strange behavior of limit clause in complex query Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 4+ messages in thread
From: Paulo Silva @ 2022-06-08 08:44 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hi,
I'm using PostgreSQL 14.3 and I'm getting strange behavior in a complex
query generated by the Entity Framework.
The inner (complex) query has a quick execution time:
# SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
FROM "dbo"."ng_content" AS "Extent1"
INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id" =
"Extent2"."id_content"
WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
timestamp)
AND 2 = "Extent1"."id_status"
AND EXISTS (
SELECT 1 AS "C1"
FROM (
SELECT "Extent3"."TagId" FROM
"dbo"."ngx_tag_content" AS "Extent3"
WHERE "Extent1"."id" = "Extent3"."ContentId"
) AS "Project1"
WHERE EXISTS (
SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
"SingleRowTable1"
WHERE "Project1"."TagId" = 337139)
)
AND ("Extent2"."id_path" IN (27495,27554,27555) AND
NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS "SingleRowTable2"
WHERE TRUE = FALSE)
);
id | C3
----------+---------------------
13505155 | 2021-03-27 12:01:00
13505187 | 2021-03-27 12:03:00
13505295 | 2021-03-27 12:06:00
13505348 | 2021-03-27 12:09:00
13505552 | 2021-03-27 12:11:00
(5 rows)
*Time: 481.826 ms*
If I run the same query as a nested select I get similar results (Q1):
*SELECT "Project5".idFROM (*
SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
FROM "dbo"."ng_content" AS "Extent1"
INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id" =
"Extent2"."id_content"
WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
timestamp)
AND 2 = "Extent1"."id_status"
AND EXISTS (
SELECT 1 AS "C1"
FROM (
SELECT "Extent3"."TagId" FROM
"dbo"."ngx_tag_content" AS "Extent3"
WHERE "Extent1"."id" = "Extent3"."ContentId"
) AS "Project1"
WHERE EXISTS (
SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
"SingleRowTable1"
WHERE "Project1"."TagId" = 337139)
)
AND ("Extent2"."id_path" IN (27495,27554,27555) AND
NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS "SingleRowTable2"
WHERE TRUE = FALSE)
)
*) AS "Project5";*
id
----------
13505155
13505187
13505295
13505348
13505552
(5 rows)
*Time: 486.174 ms*
But if I add an ORDER BY and a LIMIT something goes very wrong (Q2):
# SELECT "Project5".id
FROM (
SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
FROM "dbo"."ng_content" AS "Extent1"
INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id" =
"Extent2"."id_content"
WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
timestamp)
AND 2 = "Extent1"."id_status"
AND EXISTS (
SELECT 1 AS "C1"
FROM (
SELECT "Extent3"."TagId" FROM
"dbo"."ngx_tag_content" AS "Extent3"
WHERE "Extent1"."id" = "Extent3"."ContentId"
) AS "Project1"
WHERE EXISTS (
SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
"SingleRowTable1"
WHERE "Project1"."TagId" = 337139)
)
AND ("Extent2"."id_path" IN (27495,27554,27555) AND
NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS "SingleRowTable2"
WHERE TRUE = FALSE)
)
) AS "Project5" *ORDER BY "Project5"."C3" DESC LIMIT 6*;
id
----------
13505552
13505348
13505295
13505187
13505155
(5 rows)
*Time: 389375.374 ms (06:29.375)*
An EXPLAIN (ANALYZE, BUFFERS) for Q1 returns this:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=661.07..826757.96 rows=27943 width=4) (actual
time=446.767..492.874 rows=5 loops=1)
One-Time Filter: (NOT $1)
Buffers: shared hit=344618 read=17702 written=349
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.003
rows=0 loops=1)
One-Time Filter: false
-> Nested Loop (cost=661.07..826757.96 rows=27943 width=4) (actual
time=267.061..313.166 rows=5 loops=1)
Buffers: shared hit=344618 read=17702 written=349
-> Bitmap Heap Scan on ng_path_content "Extent2"
(cost=660.63..30053.47 rows=58752 width=4) (actual time=2.455..28.005
rows=51330 loops=1)
Recheck Cond: (id_path = ANY
('{27495,27554,27555}'::integer[]))
Heap Blocks: exact=2914
Buffers: shared hit=5 read=2963 written=35
-> Bitmap Index Scan on ng_path_content_id_path_idx
(cost=0.00..645.94 rows=58752 width=0) (actual time=2.020..2.021
rows=51332 loops=1)
Index Cond: (id_path = ANY
('{27495,27554,27555}'::integer[]))
Buffers: shared hit=5 read=47
-> Index Scan using pk_ng_content on ng_content "Extent1"
(cost=0.43..13.55 rows=1 width=4) (actual time=0.005..0.005 rows=0
loops=51330)
Index Cond: (id = "Extent2".id_content)
Filter: ((2 = id_status) AND (date_from <= LOCALTIMESTAMP)
AND (date_to >= LOCALTIMESTAMP) AND (SubPlan 1))
Rows Removed by Filter: 1
Buffers: shared hit=344613 read=14739 written=314
SubPlan 1
-> Index Only Scan using ix_ngx_tag_content on
ngx_tag_content "Extent3" (cost=0.43..8.46 rows=1 width=0) (actual
time=0.001..0.001 rows=0 loops=51327)
Index Cond: (("TagId" = 337139) AND ("ContentId" =
"Extent1".id))
Heap Fetches: 5
Buffers: shared hit=153982 read=4
Planning:
Buffers: shared hit=464 read=87 written=51
Planning Time: 5.374 ms
JIT:
Functions: 18
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 1.678 ms, Inlining 81.653 ms, Optimization 63.837 ms,
Emission 33.967 ms, Total 181.135 ms
Execution Time: 534.009 ms
(33 rows)
An EXPLAIN (ANALYZE, BUFFERS) for Q2 returns this:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.88..8979.75 rows=6 width=12) (actual
time=11037.149..183849.138 rows=5 loops=1)
Buffers: shared hit=15414548 read=564485 written=504
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001
rows=0 loops=1)
One-Time Filter: false
-> Result (cost=0.86..41816103.19 rows=27943 width=12) (actual
time=11037.146..183849.127 rows=5 loops=1)
One-Time Filter: (NOT $1)
Buffers: shared hit=15414548 read=564485 written=504
-> Nested Loop (cost=0.86..41816103.19 rows=27943 width=12)
(actual time=11037.143..183849.116 rows=5 loops=1)
Buffers: shared hit=15414548 read=564485 written=504
-> Index Scan Backward using ix_ng_content_date on
ng_content "Extent1" (cost=0.43..40616715.85 rows=2231839 width=12)
(actual time=11027.808..183839.289 rows=5 loops=1)
Filter: ((2 = id_status) AND (date_from <=
LOCALTIMESTAMP) AND (date_to >= LOCALTIMESTAMP) AND (SubPlan 1))
Rows Removed by Filter: 4685618
Buffers: shared hit=15414533 read=564480 written=504
SubPlan 1
-> Index Only Scan using ix_ngx_tag_content on
ngx_tag_content "Extent3" (cost=0.43..8.46 rows=1 width=0) (actual
time=0.003..0.003 rows=0 loops=4484963)
Index Cond: (("TagId" = 337139) AND
("ContentId" = "Extent1".id))
Heap Fetches: 5
Buffers: shared hit=13454890 read=4
-> Index Scan using ng_path_content_id_content_idx on
ng_path_content "Extent2" (cost=0.43..0.53 rows=1 width=4) (actual
time=1.956..1.958 rows=1 loops=5)
Index Cond: (id_content = "Extent1".id)
Filter: (id_path = ANY
('{27495,27554,27555}'::integer[]))
Buffers: shared hit=15 read=5
Planning:
Buffers: shared hit=474 read=76
Planning Time: 113.080 ms
Execution Time: 183849.283 ms
Can someone help me understand what's going on?
--
Paulo Silva <paulojjs@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange behavior of limit clause in complex query
2022-06-08 08:44 Strange behavior of limit clause in complex query Paulo Silva <paulojjs@gmail.com>
@ 2022-06-08 11:40 ` Ranier Vilela <ranier.vf@gmail.com>
2022-06-08 14:07 ` Re: Strange behavior of limit clause in complex query Paulo Silva <paulojjs@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Ranier Vilela @ 2022-06-08 11:40 UTC (permalink / raw)
To: Paulo Silva <paulojjs@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Em qua., 8 de jun. de 2022 às 05:44, Paulo Silva <paulojjs@gmail.com>
escreveu:
> Hi,
>
> I'm using PostgreSQL 14.3 and I'm getting strange behavior in a complex
> query generated by the Entity Framework.
>
> The inner (complex) query has a quick execution time:
>
> # SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
> FROM "dbo"."ng_content" AS "Extent1"
> INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id"
> = "Extent2"."id_content"
> WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
> AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
> timestamp)
> AND 2 = "Extent1"."id_status"
> AND EXISTS (
> SELECT 1 AS "C1"
> FROM (
> SELECT "Extent3"."TagId" FROM
> "dbo"."ngx_tag_content" AS "Extent3"
> WHERE "Extent1"."id" =
> "Extent3"."ContentId"
> ) AS "Project1"
> WHERE EXISTS (
> SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable1"
> WHERE "Project1"."TagId" = 337139)
> )
> AND ("Extent2"."id_path" IN (27495,27554,27555)
> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable2" WHERE TRUE = FALSE)
> );
> id | C3
> ----------+---------------------
> 13505155 | 2021-03-27 12:01:00
> 13505187 | 2021-03-27 12:03:00
> 13505295 | 2021-03-27 12:06:00
> 13505348 | 2021-03-27 12:09:00
> 13505552 | 2021-03-27 12:11:00
> (5 rows)
>
> *Time: 481.826 ms*
>
> If I run the same query as a nested select I get similar results (Q1):
>
>
> *SELECT "Project5".idFROM (*
> SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
> FROM "dbo"."ng_content" AS "Extent1"
> INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id"
> = "Extent2"."id_content"
> WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
> AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
> timestamp)
> AND 2 = "Extent1"."id_status"
> AND EXISTS (
> SELECT 1 AS "C1"
> FROM (
> SELECT "Extent3"."TagId" FROM
> "dbo"."ngx_tag_content" AS "Extent3"
> WHERE "Extent1"."id" =
> "Extent3"."ContentId"
> ) AS "Project1"
> WHERE EXISTS (
> SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable1"
> WHERE "Project1"."TagId" = 337139)
> )
> AND ("Extent2"."id_path" IN (27495,27554,27555)
> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable2" WHERE TRUE = FALSE)
> )
> *) AS "Project5";*
> id
> ----------
> 13505155
> 13505187
> 13505295
> 13505348
> 13505552
> (5 rows)
>
> *Time: 486.174 ms*
>
> But if I add an ORDER BY and a LIMIT something goes very wrong (Q2):
>
> # SELECT "Project5".id
> FROM (
> SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
> FROM "dbo"."ng_content" AS "Extent1"
> INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id"
> = "Extent2"."id_content"
> WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
> AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
> timestamp)
> AND 2 = "Extent1"."id_status"
> AND EXISTS (
> SELECT 1 AS "C1"
> FROM (
> SELECT "Extent3"."TagId" FROM
> "dbo"."ngx_tag_content" AS "Extent3"
> WHERE "Extent1"."id" =
> "Extent3"."ContentId"
> ) AS "Project1"
> WHERE EXISTS (
> SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable1"
> WHERE "Project1"."TagId" = 337139)
> )
> AND ("Extent2"."id_path" IN (27495,27554,27555)
> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable2" WHERE TRUE = FALSE)
> )
> ) AS "Project5" *ORDER BY "Project5"."C3" DESC LIMIT 6*;
>
I think that LIMIT is confusing the planner.
Forcing a path that in the end is not faster.
Can you try something similar to this?
WITH q AS (
SELECT "Project5".id
FROM (
SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
FROM "dbo"."ng_content" AS "Extent1"
INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id" =
"Extent2"."id_content"
WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
timestamp)
AND 2 = "Extent1"."id_status"
AND EXISTS (
SELECT 1 AS "C1"
FROM (
SELECT "Extent3"."TagId" FROM
"dbo"."ngx_tag_content" AS "Extent3"
WHERE "Extent1"."id" = "Extent3"."ContentId"
) AS "Project1"
WHERE EXISTS (
SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
"SingleRowTable1"
WHERE "Project1"."TagId" = 337139)
)
AND ("Extent2"."id_path" IN (27495,27554,27555) AND
NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS "SingleRowTable2"
WHERE TRUE = FALSE))
))
SELECT * FROM q ORDER BY q.C3 DESC LIMIT 6;
Probably, using CTE, the plan you want.
regards,
Ranier Vilela
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange behavior of limit clause in complex query
2022-06-08 08:44 Strange behavior of limit clause in complex query Paulo Silva <paulojjs@gmail.com>
2022-06-08 11:40 ` Re: Strange behavior of limit clause in complex query Ranier Vilela <ranier.vf@gmail.com>
@ 2022-06-08 14:07 ` Paulo Silva <paulojjs@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Paulo Silva @ 2022-06-08 14:07 UTC (permalink / raw)
To: Ranier Vilela <ranier.vf@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
The problem is that the query is generated by the framework, I'm not sure
if I can change anything on it. Any other way to influence planner?
Regards
Ranier Vilela <ranier.vf@gmail.com> escreveu no dia quarta, 8/06/2022 à(s)
12:40:
> Em qua., 8 de jun. de 2022 às 05:44, Paulo Silva <paulojjs@gmail.com>
> escreveu:
>
>> Hi,
>>
>> I'm using PostgreSQL 14.3 and I'm getting strange behavior in a complex
>> query generated by the Entity Framework.
>>
>> The inner (complex) query has a quick execution time:
>>
>> # SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
>> FROM "dbo"."ng_content" AS "Extent1"
>> INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id"
>> = "Extent2"."id_content"
>> WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
>> AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
>> timestamp)
>> AND 2 = "Extent1"."id_status"
>> AND EXISTS (
>> SELECT 1 AS "C1"
>> FROM (
>> SELECT "Extent3"."TagId" FROM
>> "dbo"."ngx_tag_content" AS "Extent3"
>> WHERE "Extent1"."id" =
>> "Extent3"."ContentId"
>> ) AS "Project1"
>> WHERE EXISTS (
>> SELECT 1 AS "C1" FROM (SELECT 1 AS "C")
>> AS "SingleRowTable1"
>> WHERE "Project1"."TagId" = 337139)
>> )
>> AND ("Extent2"."id_path" IN (27495,27554,27555)
>> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
>> "SingleRowTable2" WHERE TRUE = FALSE)
>> );
>> id | C3
>> ----------+---------------------
>> 13505155 | 2021-03-27 12:01:00
>> 13505187 | 2021-03-27 12:03:00
>> 13505295 | 2021-03-27 12:06:00
>> 13505348 | 2021-03-27 12:09:00
>> 13505552 | 2021-03-27 12:11:00
>> (5 rows)
>>
>> *Time: 481.826 ms*
>>
>> If I run the same query as a nested select I get similar results (Q1):
>>
>>
>> *SELECT "Project5".idFROM (*
>> SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
>> FROM "dbo"."ng_content" AS "Extent1"
>> INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id"
>> = "Extent2"."id_content"
>> WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
>> AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
>> timestamp)
>> AND 2 = "Extent1"."id_status"
>> AND EXISTS (
>> SELECT 1 AS "C1"
>> FROM (
>> SELECT "Extent3"."TagId" FROM
>> "dbo"."ngx_tag_content" AS "Extent3"
>> WHERE "Extent1"."id" =
>> "Extent3"."ContentId"
>> ) AS "Project1"
>> WHERE EXISTS (
>> SELECT 1 AS "C1" FROM (SELECT 1 AS "C")
>> AS "SingleRowTable1"
>> WHERE "Project1"."TagId" = 337139)
>> )
>> AND ("Extent2"."id_path" IN (27495,27554,27555)
>> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
>> "SingleRowTable2" WHERE TRUE = FALSE)
>> )
>> *) AS "Project5";*
>> id
>> ----------
>> 13505155
>> 13505187
>> 13505295
>> 13505348
>> 13505552
>> (5 rows)
>>
>> *Time: 486.174 ms*
>>
>> But if I add an ORDER BY and a LIMIT something goes very wrong (Q2):
>>
>> # SELECT "Project5".id
>> FROM (
>> SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
>> FROM "dbo"."ng_content" AS "Extent1"
>> INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id"
>> = "Extent2"."id_content"
>> WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
>> AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
>> timestamp)
>> AND 2 = "Extent1"."id_status"
>> AND EXISTS (
>> SELECT 1 AS "C1"
>> FROM (
>> SELECT "Extent3"."TagId" FROM
>> "dbo"."ngx_tag_content" AS "Extent3"
>> WHERE "Extent1"."id" =
>> "Extent3"."ContentId"
>> ) AS "Project1"
>> WHERE EXISTS (
>> SELECT 1 AS "C1" FROM (SELECT 1 AS "C")
>> AS "SingleRowTable1"
>> WHERE "Project1"."TagId" = 337139)
>> )
>> AND ("Extent2"."id_path" IN (27495,27554,27555)
>> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
>> "SingleRowTable2" WHERE TRUE = FALSE)
>> )
>> ) AS "Project5" *ORDER BY "Project5"."C3" DESC LIMIT 6*;
>>
> I think that LIMIT is confusing the planner.
> Forcing a path that in the end is not faster.
>
> Can you try something similar to this?
>
> WITH q AS (
> SELECT "Project5".id
> FROM (
> SELECT "Extent1"."id", CAST ("Extent1"."date" AS timestamp) AS "C3"
> FROM "dbo"."ng_content" AS "Extent1"
> INNER JOIN "dbo"."ng_path_content" AS "Extent2" ON "Extent1"."id"
> = "Extent2"."id_content"
> WHERE "Extent1"."date_from" <= CAST (LOCALTIMESTAMP AS timestamp)
> AND "Extent1"."date_to" >= CAST (LOCALTIMESTAMP AS
> timestamp)
> AND 2 = "Extent1"."id_status"
> AND EXISTS (
> SELECT 1 AS "C1"
> FROM (
> SELECT "Extent3"."TagId" FROM
> "dbo"."ngx_tag_content" AS "Extent3"
> WHERE "Extent1"."id" =
> "Extent3"."ContentId"
> ) AS "Project1"
> WHERE EXISTS (
> SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable1"
> WHERE "Project1"."TagId" = 337139)
> )
> AND ("Extent2"."id_path" IN (27495,27554,27555)
> AND NOT EXISTS (SELECT 1 AS "C1" FROM (SELECT 1 AS "C") AS
> "SingleRowTable2" WHERE TRUE = FALSE))
> ))
> SELECT * FROM q ORDER BY q.C3 DESC LIMIT 6;
>
> Probably, using CTE, the plan you want.
>
> regards,
> Ranier Vilela
>
--
Paulo Silva <paulojjs@gmail.com>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Strange behavior of limit clause in complex query
2022-06-08 08:44 Strange behavior of limit clause in complex query Paulo Silva <paulojjs@gmail.com>
@ 2022-06-08 14:32 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2022-06-08 14:32 UTC (permalink / raw)
To: Paulo Silva <paulojjs@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Wed, Jun 08, 2022 at 09:44:08AM +0100, Paulo Silva wrote:
> But if I add an ORDER BY and a LIMIT something goes very wrong (Q2):
A somewhat common problem.
A common workaround is to change "ORDER BY a" to something like "ORDER BY a+0"
(if your framework will allow it).
> An EXPLAIN (ANALYZE, BUFFERS) for Q2 returns this:
...
> -> Index Scan Backward using ix_ng_content_date on ng_content "Extent1" (cost=0.43..40616715.85 rows=2231839 width=12) (actual time=11027.808..183839.289 rows=5 loops=1)
> Filter: ((2 = id_status) AND (date_from <= LOCALTIMESTAMP) AND (date_to >= LOCALTIMESTAMP) AND (SubPlan 1))
> Rows Removed by Filter: 4685618
> Buffers: shared hit=15414533 read=564480 written=504
I'm not sure if it would help your original issue, but the rowcount estimate
here is bad - overestimating 2231839 rows instead of 5.
Could you try to determine which of those conditions (id_status, date_from,
date_to, or SubPlan) causes the mis-estimate, or if the estimate is only wrong
when they're combined ?
--
Justin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-06-08 14:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-08 08:44 Strange behavior of limit clause in complex query Paulo Silva <paulojjs@gmail.com>
2022-06-08 11:40 ` Ranier Vilela <ranier.vf@gmail.com>
2022-06-08 14:07 ` Paulo Silva <paulojjs@gmail.com>
2022-06-08 14:32 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox