pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostrgesql query planner wrong desicion
7+ messages / 3 participants
[nested] [flat]
* postrgesql query planner wrong desicion
@ 2022-06-17 08:48 Kenny Bachman <kenny.bachman17@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Kenny Bachman @ 2022-06-17 08:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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']))));
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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postrgesql query planner wrong desicion
@ 2022-06-17 14:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kenny Bachman <kenny.bachman17@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2022-06-17 14:19 UTC (permalink / raw)
To: Kenny Bachman <kenny.bachman17@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postrgesql query planner wrong desicion
@ 2022-06-18 06:41 Kenny Bachman <kenny.bachman17@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Kenny Bachman @ 2022-06-18 06:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postrgesql query planner wrong desicion
@ 2022-06-18 15:36 Jeff Janes <jeff.janes@gmail.com>
parent: Kenny Bachman <kenny.bachman17@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jeff Janes @ 2022-06-18 15:36 UTC (permalink / raw)
To: Kenny Bachman <kenny.bachman17@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postrgesql query planner wrong desicion
@ 2022-06-19 20:33 Jeff Janes <jeff.janes@gmail.com>
parent: Kenny Bachman <kenny.bachman17@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Jeff Janes @ 2022-06-19 20:33 UTC (permalink / raw)
To: Kenny Bachman <kenny.bachman17@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postrgesql query planner wrong desicion
@ 2022-06-20 04:54 Kenny Bachman <kenny.bachman17@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kenny Bachman @ 2022-06-20 04:54 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postrgesql query planner wrong desicion
@ 2022-06-20 17:49 Jeff Janes <jeff.janes@gmail.com>
parent: Kenny Bachman <kenny.bachman17@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jeff Janes @ 2022-06-20 17:49 UTC (permalink / raw)
To: Kenny Bachman <kenny.bachman17@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-06-20 17:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-17 08:48 postrgesql query planner wrong desicion Kenny Bachman <kenny.bachman17@gmail.com>
2022-06-17 14:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-06-18 06:41 ` Kenny Bachman <kenny.bachman17@gmail.com>
2022-06-19 20:33 ` Jeff Janes <jeff.janes@gmail.com>
2022-06-20 04:54 ` Kenny Bachman <kenny.bachman17@gmail.com>
2022-06-20 17:49 ` Jeff Janes <jeff.janes@gmail.com>
2022-06-18 15:36 ` Jeff Janes <jeff.janes@gmail.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