Обсуждение: postrgesql query planner wrong desicion
Hello Team,
I have a 12.11 postgresql database on Linux. When I ran the below query, the query planner uses the gist index. However, the query runs faster if I drop the gist index, because the query planner uses the b-tree index. Why does the query planner use the gist index instead of the b-tree?
SELECT DISTINCT w."InstanceId",
w."LastStepId" ,
sl."SlaPercent",
sl."RemainingSla" ,
sl."DefaultSla" ,
sl."PriorityScore",
sl."KpiDuration" ,
sl."E2EDurationHour" ,
sl."E2EDurationDay" ,
sl."E2EDurationHour" ,
sl."E2EDurationDay" ,
sl."TeamDuration" AS "GroupTime",
poolsource."PartyId",
sl."SLASTATUS"
FROM dsi."Pool_Datasource" poolsource
JOIN dsi."Instance" w ON poolsource."InstanceId" = w."InstanceId"
AND NOT (EXISTS ( SELECT 1 FROM dsi."Pool_Party" WHERE "Pool_Party"."Code" = 'TEAM-FIXPOWERUSER'
AND poolsource."RelatedPartyId" = "Pool_Party"."PartyId"))
AND w."ApplicationCd" = 'SolIncident'
LEFT JOIN dsi."Records" sl ON sl."InstanceId" = w."InstanceId"
LEFT JOIN dsi."Tickets" tc ON tc."WFINSTANCEID" = w."InstanceId"
WHERE w."LastStepId" = poolsource."StepId" AND w."EntityStatusCd" <> 'CLOSE' AND
NOT (EXISTS (SELECT 1 FROM dsi."Tickets" tc_1
WHERE tc_1."OLDPLATFORMCODE" = 'Davinci' AND tc_1."TICKETTYPEID" = 1
AND w."InstanceId" = tc_1."WFINSTANCEID"
AND (tc_1."STATUS" = ANY (ARRAY['SOLUTION', 'REJECT']))));
w."LastStepId" ,
sl."SlaPercent",
sl."RemainingSla" ,
sl."DefaultSla" ,
sl."PriorityScore",
sl."KpiDuration" ,
sl."E2EDurationHour" ,
sl."E2EDurationDay" ,
sl."E2EDurationHour" ,
sl."E2EDurationDay" ,
sl."TeamDuration" AS "GroupTime",
poolsource."PartyId",
sl."SLASTATUS"
FROM dsi."Pool_Datasource" poolsource
JOIN dsi."Instance" w ON poolsource."InstanceId" = w."InstanceId"
AND NOT (EXISTS ( SELECT 1 FROM dsi."Pool_Party" WHERE "Pool_Party"."Code" = 'TEAM-FIXPOWERUSER'
AND poolsource."RelatedPartyId" = "Pool_Party"."PartyId"))
AND w."ApplicationCd" = 'SolIncident'
LEFT JOIN dsi."Records" sl ON sl."InstanceId" = w."InstanceId"
LEFT JOIN dsi."Tickets" tc ON tc."WFINSTANCEID" = w."InstanceId"
WHERE w."LastStepId" = poolsource."StepId" AND w."EntityStatusCd" <> 'CLOSE' AND
NOT (EXISTS (SELECT 1 FROM dsi."Tickets" tc_1
WHERE tc_1."OLDPLATFORMCODE" = 'Davinci' AND tc_1."TICKETTYPEID" = 1
AND w."InstanceId" = tc_1."WFINSTANCEID"
AND (tc_1."STATUS" = ANY (ARRAY['SOLUTION', 'REJECT']))));
With GIST Index: (Total exec time : 34s)
Index Scan using "Pool_Party_Code_gist" on "Pool_Party" (cost=0.28..8.30 rows=1 width=4) (actual time=0.097..0.097 rows=1 loops=330870)
Index Cond: (("Code")::text = 'TEAM-FIXPOWERUSER'::text)
Index Cond: (("Code")::text = 'TEAM-FIXPOWERUSER'::text)
Without GIST Index: (Total exec time: 4s)
Index Scan using "Pool_Party_Code_idx" on "Pool_Party" (cost=0.42..8.44 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=330870)
Index Cond: (("Code")::text = 'TEAM-FIXPOWERUSER'::text)
Index Cond: (("Code")::text = 'TEAM-FIXPOWERUSER'::text)
Kenny Bachman <kenny.bachman17@gmail.com> writes:
> With GIST Index: (Total exec time : 34s)
> Index Scan using "Pool_Party_Code_gist" on "Pool_Party" (cost=0.28..8.30
> rows=1 width=4) (actual time=0.097..0.097 rows=1 loops=330870)
> Index Cond: (("Code")::text =
> 'TEAM-FIXPOWERUSER'::text)
> Without GIST Index: (Total exec time: 4s)
> Index Scan using "Pool_Party_Code_idx" on "Pool_Party" (cost=0.42..8.44
> rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=330870)
> Index Cond: (("Code")::text =
> 'TEAM-FIXPOWERUSER'::text)
Why do you have a gist index on a column that's apparently plain
text (or varchar)? It seems kinda pointless, and the more so if
it duplicates a btree index.
regards, tom lane
Hi Tom,
The gist index is used by other queries with LIKE or ILIKE operators. Should I drop the gist index for text or varchar columns?
Tom Lane <tgl@sss.pgh.pa.us>, 17 Haz 2022 Cum, 17:19 tarihinde şunu yazdı:
Kenny Bachman <kenny.bachman17@gmail.com> writes:
> With GIST Index: (Total exec time : 34s)
> Index Scan using "Pool_Party_Code_gist" on "Pool_Party" (cost=0.28..8.30
> rows=1 width=4) (actual time=0.097..0.097 rows=1 loops=330870)
> Index Cond: (("Code")::text =
> 'TEAM-FIXPOWERUSER'::text)
> Without GIST Index: (Total exec time: 4s)
> Index Scan using "Pool_Party_Code_idx" on "Pool_Party" (cost=0.42..8.44
> rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=330870)
> Index Cond: (("Code")::text =
> 'TEAM-FIXPOWERUSER'::text)
Why do you have a gist index on a column that's apparently plain
text (or varchar)? It seems kinda pointless, and the more so if
it duplicates a btree index.
regards, tom lane
On Fri, Jun 17, 2022 at 4:48 AM Kenny Bachman <kenny.bachman17@gmail.com> wrote:
Hello Team,I have a 12.11 postgresql database on Linux. When I ran the below query, the query planner uses the gist index. However, the query runs faster if I drop the gist index, because the query planner uses the b-tree index. Why does the query planner use the gist index instead of the b-tree?
Please show the definition of both indexes. Also, eliminate the joins and where clause conditions which are not needed to reproduce the base problem, then show the full plans, not just one line.
Cheers,
Jeff
On Sat, Jun 18, 2022 at 2:42 AM Kenny Bachman <kenny.bachman17@gmail.com> wrote:
Hi Tom,The gist index is used by other queries with LIKE or ILIKE operators. Should I drop the gist index for text or varchar columns?
This story doesn't make sense to me. The gist operator for text provided by btree_gist does not support LIKE (other than in the same way btree indexing does), so there is no point in making one of those indexes for this purpose. And the gist operator for text provided by pg_trgm does not support equality (until PostgreSQL v14) so that type of index would not "capture" the equality comparison in v12.11. If not one of those two, then where are you getting your gist operator class from?
That is not to say the costing of GiST indexes shouldn't be improved, but I don't see how it could sensibly be causing this problem under v12.
Cheers,
Jeff
Hi Jeff,
Thank you so much for your explanation. I realized that the gist index was used many many times. So, I couldn't drop the gist index permanently. My indexes definitions for the "Code" varchar column, are below:
"Pool_Party_Code_gist" gist ("Code")
"Pool_Party_Code_idx" btree ("Code")
"Pool_Party_Code_idx1" gist ("Code" gist_trgm_ops)
The B-tree index is not used by the planner for equality queries. It uses the gist index. I did REINDEX, VACUUM, and ANALYZE for the table and all indexes, but the result did not change. For a basic example;
EXPLAIN ANALYZE SELECT * FROM dsi."Pool_Party" where "Code" = 'TEAM-FIXPOWERUSER';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using "Pool_Party_Code_gist" on "Pool_Party" (cost=0.28..8.30 rows=1 width=502) (actual time=0.485..0.550 rows=1 loops=1)
Index Cond: (("Code")::text = 'TEAM-FIXPOWERUSER'::text)
Planning Time: 0.823 ms
Execution Time: 0.586 ms
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using "Pool_Party_Code_gist" on "Pool_Party" (cost=0.28..8.30 rows=1 width=502) (actual time=0.485..0.550 rows=1 loops=1)
Index Cond: (("Code")::text = 'TEAM-FIXPOWERUSER'::text)
Planning Time: 0.823 ms
Execution Time: 0.586 ms
Jeff Janes <jeff.janes@gmail.com>, 19 Haz 2022 Paz, 23:33 tarihinde şunu yazdı:
On Sat, Jun 18, 2022 at 2:42 AM Kenny Bachman <kenny.bachman17@gmail.com> wrote:Hi Tom,The gist index is used by other queries with LIKE or ILIKE operators. Should I drop the gist index for text or varchar columns?This story doesn't make sense to me. The gist operator for text provided by btree_gist does not support LIKE (other than in the same way btree indexing does), so there is no point in making one of those indexes for this purpose. And the gist operator for text provided by pg_trgm does not support equality (until PostgreSQL v14) so that type of index would not "capture" the equality comparison in v12.11. If not one of those two, then where are you getting your gist operator class from?That is not to say the costing of GiST indexes shouldn't be improved, but I don't see how it could sensibly be causing this problem under v12.Cheers,Jeff
On Mon, Jun 20, 2022 at 12:54 AM Kenny Bachman <kenny.bachman17@gmail.com> wrote:
Hi Jeff,Thank you so much for your explanation. I realized that the gist index was used many many times. So, I couldn't drop the gist index permanently.
The point of your question is that it is being used even though it is slower. So the fact that it is used many times doesn't mean anything, we already know it is used. The better question is, are all of those uses ones which could be done just as well or better with the btree? I don't see any good ways to answer that definitively, other than by just trying the drop. But I suspect the answer is 'yes'. As far as I know, the only reason to use btree_gist is so you can get access to multi-column gist indexes in which one of the *other* columns needs/benefits from gist. Using it for a single-column index just doesn't make sense to me.
Cheers,
Jeff