pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBad query plan decision when using multiple column index - postgresql uses only first column then filters
12+ messages / 4 participants
[nested] [flat]
* Bad query plan decision when using multiple column index - postgresql uses only first column then filters
@ 2020-01-16 14:06 Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Cosmin Prund @ 2020-01-16 14:06 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello List, I'm Cosmin. This is my first post and I'll get right down to
the problem. I'm using Postgresql 10 (because that's what's installed by
default on Ubuntu 18.04):
explain analyze
select R, C, V from LBD
where Ver = 92 and Id in (10,11)
Index Scan using "IX_LBD_Ver_Id" on "LBD" (cost=0.56..2.37 rows=1
width=13) (actual time=0.063..857.725 rows=2 loops=1)
Index Cond: ("Ver" = 92)
Filter: ("Id" = ANY ('{10,11}'::integer[]))
Rows Removed by Filter: 1869178
Planning time: 0.170 ms
Execution time: 857.767 ms
The IX_LBD_Ver_Id index is on two columns (Ver, Id) - it's not in "Ver"
alone!
Somehow the query planner thinks that scanning the index on "Ver" alone
should only return 1 record. The problem is that there are, on average,
millions of records for each "Ver"!
The current query is not my real query: the original problem was with a
JOIN. I boiled it down to this simple query because it shows the same
problem: when dealing with more then one "Id" the planner scans on "Ver"
and filters on "Id". Running the query with a single "Id" does use the
index on both columns and the query finishes in only 0.7 ms (one thousand
times faster)
The planner doesn't always get it this bad. The original JOIN usually runs
instantaneously. Unless the planner gets into it's current funk and then
the original JOIN never finishes.
- I've reindexed the whole database
- I ran ANALYZE on all tables
- I checked "pg_stats", here are the stats:
select attname, null_frac, avg_width, n_distinct, correlation from pg_stats
where tablename = 'LBD' and (attname in ('Id', 'Ver'))
attname null_frac acg_width n_distinct correlation
Id 0 4 2029846 0.0631249
Ver 0 2 22 0.624823
According to data from "pg_stats" the query planner should know that
scanning the "LBD" table has on average millions of records per "Ver".
The fact that this works right most of the time tells me I'm dealing with
some kind of statistical information (something along the lines of
n_distinct from pg_stat) and gives me hope. Once I know why the planner
gets this wrong I should be able to make it right.
Please point me in the right direction. Where should I look, what should I
try?
Thank you,
Cosmin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
@ 2020-01-16 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 16:59 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Michael Lewis <mlewis@entrata.com>
2020-01-16 17:18 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2020-01-16 15:11 UTC (permalink / raw)
To: Cosmin Prund <cprund@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Cosmin Prund <cprund@gmail.com> writes:
> explain analyze
> select R, C, V from LBD
> where Ver = 92 and Id in (10,11)
> Index Scan using "IX_LBD_Ver_Id" on "LBD" (cost=0.56..2.37 rows=1
> width=13) (actual time=0.063..857.725 rows=2 loops=1)
> Index Cond: ("Ver" = 92)
> Filter: ("Id" = ANY ('{10,11}'::integer[]))
> Rows Removed by Filter: 1869178
> Planning time: 0.170 ms
> Execution time: 857.767 ms
> The IX_LBD_Ver_Id index is on two columns (Ver, Id) - it's not in "Ver"
> alone!
Seems like an odd choice of plan, then, but you haven't provided any
detail that would let anyone guess why it's not using the second index
column. For starters it would be good to show the exact table and
index schema (eg via \d+ in psql). Also, does explicitly ANALYZE'ing
the table change anything?
https://wiki.postgresql.org/wiki/Slow_Query_Questions
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-01-16 16:59 ` Michael Lewis <mlewis@entrata.com>
2020-01-16 17:27 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 18:15 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
1 sibling, 2 replies; 12+ messages in thread
From: Michael Lewis @ 2020-01-16 16:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Cosmin Prund <cprund@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Does the behavior change with different values of Ver column? I'd be
curious of the fraction in the MCVs frequency list in stats indicates that
rows with Ver = 92 are rare and therefore the index on only Ver column is
sufficient to find the rows quickly. What is reltuples for this table by
the way?
I also wonder if the situation may be helped by re-indexing the "index on
both columns" to remove any chance of issues on bloat in the index. Which
order are the columns by the way? If Ver is first, is there also an index
on only id column?. Since you aren't on v12, you don't get to re-index
concurrently but I assume you know the work around of create concurrently
(different name), drop concurrently (old one), and finally rename new index.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 16:59 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Michael Lewis <mlewis@entrata.com>
@ 2020-01-16 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2020-01-16 17:27 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Cosmin Prund <cprund@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Michael Lewis <mlewis@entrata.com> writes:
> Does the behavior change with different values of Ver column?
By and large, indxpath.c will just add all qual clauses that match
an index to the indexscan's conditions --- there's no attempt to
decide that some of them might not be worth it on cost grounds.
So I'd be pretty surprised if altering the Ver constant made any
difference. My money is on there being some reason why the IN
clause doesn't match the index, perhaps a type mismatch. Without
seeing the table schema, and the exact query, it's hard to say what
that reason is. (I'll not insult your intelligence by saying how
I know that the OP didn't just copy-and-paste that query.)
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 16:59 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Michael Lewis <mlewis@entrata.com>
@ 2020-01-16 18:15 ` Cosmin Prund <cprund@gmail.com>
2020-01-16 18:52 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Michael Lewis <mlewis@entrata.com>
2020-01-16 18:55 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 12+ messages in thread
From: Cosmin Prund @ 2020-01-16 18:15 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hello Michael and hello again Tom, sorry for mailing you directly. I just
hit Reply in gmail - I expected the emails to have a reply-to=Pgsql.
Apparently they do not.
Running the same query with a different "Ver" produces a proper plan.
Here's a non-redacted example (Ver=91):
EXPLAIN (ANALYZE, BUFFERS) select "IdRand", "IdColoana", "Valoare" from
"LucrareBugetDate" where ("LucrareBugetVersiuneId" = 91) and
("LucrareBugetDateId" in (10,11));
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using
"IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" on
"LucrareBugetDate" (cost=0.56..4.95 rows=2 width=13) (actual
time=3.617..3.631 rows=2 loops=1)
Index Cond: (("LucrareBugetVersiuneId" = 91) AND ("LucrareBugetDateId" =
ANY ('{10,11}'::integer[])))
Buffers: shared hit=9 read=3
Planning time: 0.223 ms
Execution time: 3.663 ms
(5 rows)
I have reindex everything, not just this INDEX.
"reltuples" for this table is 41712436.
> I'd be curious of the fraction in the MCVs frequency list in stats
indicates that rows with Ver = 92 are rare and therefore the index on
only Ver column is sufficient to find the rows quickly.
There are 25 valid values for "Ver" in this database. I ran the query for
all of them. The only one miss-behaving is "92". I ran the query with
random values for Ver (invalid values), the query plan always attempts to
use the index using both values.
I looked into "most_common_values" in pg_stats, this value (92) is not in
that list.
Finally I ran "ANALYZE" again and now the problem went away. Running the
query with Ver=92 uses the proper plan. I'm not happy with this - I know I
haven't solved the problem (I've ran ANALYZE multiple times before).
On Thu, 16 Jan 2020 at 19:00, Michael Lewis <mlewis@entrata.com> wrote:
> Does the behavior change with different values of Ver column? I'd be
> curious of the fraction in the MCVs frequency list in stats indicates that
> rows with Ver = 92 are rare and therefore the index on only Ver column is
> sufficient to find the rows quickly. What is reltuples for this table by
> the way?
>
> I also wonder if the situation may be helped by re-indexing the "index on
> both columns" to remove any chance of issues on bloat in the index. Which
> order are the columns by the way? If Ver is first, is there also an index
> on only id column?. Since you aren't on v12, you don't get to re-index
> concurrently but I assume you know the work around of create concurrently
> (different name), drop concurrently (old one), and finally rename new index.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 16:59 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Michael Lewis <mlewis@entrata.com>
2020-01-16 18:15 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
@ 2020-01-16 18:52 ` Michael Lewis <mlewis@entrata.com>
1 sibling, 0 replies; 12+ messages in thread
From: Michael Lewis @ 2020-01-16 18:52 UTC (permalink / raw)
To: Cosmin Prund <cprund@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
"Finally I ran "ANALYZE" again and now the problem went away. Running the
query with Ver=92 uses the proper plan. I'm not happy with this - I know I
haven't solved the problem (I've ran ANALYZE multiple times before)."
Does 92 appear in MCVs list with that new sampling? I wonder if
default_statistics_target should be increased a bit to help ensure a
thorough sample of the data in this table. Note- don't go too high (maybe
250, not 1000) or planning time can increase significantly. Also, perhaps
only increase on this Ver column.
What is the real frequency of value 92? With default_statistics_target =
100, analyze takes 100*300 rows as sample, and if it is missed in that 30k
rows set, or very very small when in fact it has equal weight with other
values, then during planning time it is expected to be very very rare when
in fact it is only slightly less common than the others in the list. If the
others in the list are expected to be 100% of the table as you showed with
the query to compute "frac_MCV" from pg_stats for that column, then perhaps
the optimizer is wise to scan only the LucrareBugetVersiuneId column of the
composite index and filter in memory.
Curious, when you get bad plans (re-analyze the table repeatedly to get new
samples until the wrong plan is chosen), what does PG estimate for total
rows returned with ONLY LucrareBugetVersiuneId = 92 as the where condition?
Note- Tom & Laurenz are real experts. I might have no idea what I am doing
yet. It is too early to say.
On Thu, Jan 16, 2020 at 11:15 AM Cosmin Prund <cprund@gmail.com> wrote:
> Hello Michael and hello again Tom, sorry for mailing you directly. I just
> hit Reply in gmail - I expected the emails to have a reply-to=Pgsql.
> Apparently they do not.
>
> Running the same query with a different "Ver" produces a proper plan.
> Here's a non-redacted example (Ver=91):
>
> EXPLAIN (ANALYZE, BUFFERS) select "IdRand", "IdColoana", "Valoare" from
> "LucrareBugetDate" where ("LucrareBugetVersiuneId" = 91) and
> ("LucrareBugetDateId" in (10,11));
>
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using
> "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" on
> "LucrareBugetDate" (cost=0.56..4.95 rows=2 width=13) (actual
> time=3.617..3.631 rows=2 loops=1)
> Index Cond: (("LucrareBugetVersiuneId" = 91) AND ("LucrareBugetDateId"
> = ANY ('{10,11}'::integer[])))
> Buffers: shared hit=9 read=3
> Planning time: 0.223 ms
> Execution time: 3.663 ms
> (5 rows)
>
> I have reindex everything, not just this INDEX.
>
> "reltuples" for this table is 41712436.
>
> > I'd be curious of the fraction in the MCVs frequency list in stats
> indicates that rows with Ver = 92 are rare and therefore the index on
> only Ver column is sufficient to find the rows quickly.
>
> There are 25 valid values for "Ver" in this database. I ran the query for
> all of them. The only one miss-behaving is "92". I ran the query with
> random values for Ver (invalid values), the query plan always attempts to
> use the index using both values.
> I looked into "most_common_values" in pg_stats, this value (92) is not in
> that list.
> Finally I ran "ANALYZE" again and now the problem went away. Running the
> query with Ver=92 uses the proper plan. I'm not happy with this - I know I
> haven't solved the problem (I've ran ANALYZE multiple times before).
>
>
> On Thu, 16 Jan 2020 at 19:00, Michael Lewis <mlewis@entrata.com> wrote:
>
>> Does the behavior change with different values of Ver column? I'd be
>> curious of the fraction in the MCVs frequency list in stats indicates that
>> rows with Ver = 92 are rare and therefore the index on only Ver column is
>> sufficient to find the rows quickly. What is reltuples for this table by
>> the way?
>>
>> I also wonder if the situation may be helped by re-indexing the "index on
>> both columns" to remove any chance of issues on bloat in the index. Which
>> order are the columns by the way? If Ver is first, is there also an index
>> on only id column?. Since you aren't on v12, you don't get to re-index
>> concurrently but I assume you know the work around of create concurrently
>> (different name), drop concurrently (old one), and finally rename new index.
>>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 16:59 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Michael Lewis <mlewis@entrata.com>
2020-01-16 18:15 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
@ 2020-01-16 18:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 19:52 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2020-01-16 18:55 UTC (permalink / raw)
To: Cosmin Prund <cprund@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Cosmin Prund <cprund@gmail.com> writes:
> Running the same query with a different "Ver" produces a proper plan.
Oh, that *is* interesting.
After studying the code a bit more I see why this is possible when I
originally thought not. The case that you are interested in is one that
has special handling -- it's a "lower-order ScalarArrayOpExpr" in the
terms of the code. This means that get_index_paths will actually produce
two index paths, one with the IN clause as an indexqual and one without,
because it expects that they have different sort behaviors [1]. So then
we do have a chance for a cost-based choice, making it possible for the
estimated selectivity of the higher-order clause to affect the outcome.
I'm still a bit surprised that it wouldn't choose the alternative with
the IN ... but if the estimated number of rows matching just the first
column is small enough, it might see the paths as having indistinguishable
costs, and then it's down to luck which it chooses.
> There are 25 valid values for "Ver" in this database. I ran the query for
> all of them. The only one miss-behaving is "92". I ran the query with
> random values for Ver (invalid values), the query plan always attempts to
> use the index using both values.
> I looked into "most_common_values" in pg_stats, this value (92) is not in
> that list.
Are the other 24 all in the list?
> Finally I ran "ANALYZE" again and now the problem went away. Running the
> query with Ver=92 uses the proper plan. I'm not happy with this - I know I
> haven't solved the problem (I've ran ANALYZE multiple times before).
Maybe increasing the stats target for the "Ver" column would help. It
sounds like you want to get to a point where all the valid values are
given in the MCV list, so that the estimates for them will be accurate.
regards, tom lane
[1] Right at the moment, it seems like that's wrong and we could just
generate one path. Need to study this.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 16:59 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Michael Lewis <mlewis@entrata.com>
2020-01-16 18:15 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 18:55 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-01-16 19:52 ` Cosmin Prund <cprund@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Cosmin Prund @ 2020-01-16 19:52 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
most_common_values before the ANALYZE had 22 values. After ANALYZE it has
23 values.
After ANALYZE I get an entry for "92" with 0.0441333 frequency (the
frequency is about right).
The stats target for the "Ver" column is already at 10000. I'm going to
have to bring the stats target back on everything, but I'm not sure about
this. The life-cycle of this table is a bit special. Once-in-a-while a new
"Version" is created: 1 to 3 million records are inserted at once, all with
the same Version and with sequential Id-s (re-starting from 1 with each
version). The unfortunate side-effect is that I get huge clusters of
records with the same "Ver". I created a script that calculates the correct
"n_distinct" value for the column and repeatedly runs ANALYZE until the
reported "n_distinct" value is grater then 75% of the correct number; on
each loop of the script the stats target is increased by 5%. I thought this
would help me find a good value for the stats target but it invariably
brings the stats target all the way up to 10000.
Finally I have one last option: take "stats" into my own hands. Since
inserting anything into those tables is such a big (but rare and well
defined) event, I could simply set the stats target to ZERO and compute
correct values on my own after pushing a new version. The issue here is
that I don't understand the system well-enough to make this work.
Hopefully I'll be able to reproduce this on a backup of the database so I
can safely experiment. Until I manage to reproduce this I don't think I can
make any more progress, so thank you everyone for the help.
On Thu, 16 Jan 2020 at 20:55, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Cosmin Prund <cprund@gmail.com> writes:
> > Running the same query with a different "Ver" produces a proper plan.
>
> Oh, that *is* interesting.
>
> After studying the code a bit more I see why this is possible when I
> originally thought not. The case that you are interested in is one that
> has special handling -- it's a "lower-order ScalarArrayOpExpr" in the
> terms of the code. This means that get_index_paths will actually produce
> two index paths, one with the IN clause as an indexqual and one without,
> because it expects that they have different sort behaviors [1]. So then
> we do have a chance for a cost-based choice, making it possible for the
> estimated selectivity of the higher-order clause to affect the outcome.
>
> I'm still a bit surprised that it wouldn't choose the alternative with
> the IN ... but if the estimated number of rows matching just the first
> column is small enough, it might see the paths as having indistinguishable
> costs, and then it's down to luck which it chooses.
>
> > There are 25 valid values for "Ver" in this database. I ran the query for
> > all of them. The only one miss-behaving is "92". I ran the query with
> > random values for Ver (invalid values), the query plan always attempts to
> > use the index using both values.
> > I looked into "most_common_values" in pg_stats, this value (92) is not in
> > that list.
>
> Are the other 24 all in the list?
>
> > Finally I ran "ANALYZE" again and now the problem went away. Running the
> > query with Ver=92 uses the proper plan. I'm not happy with this - I know
> I
> > haven't solved the problem (I've ran ANALYZE multiple times before).
>
> Maybe increasing the stats target for the "Ver" column would help. It
> sounds like you want to get to a point where all the valid values are
> given in the MCV list, so that the estimates for them will be accurate.
>
> regards, tom lane
>
> [1] Right at the moment, it seems like that's wrong and we could just
> generate one path. Need to study this.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-01-16 17:18 ` Cosmin Prund <cprund@gmail.com>
2020-01-16 18:20 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Laurenz Albe <laurenz.albe@cybertec.at>
2020-01-16 18:23 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 12+ messages in thread
From: Cosmin Prund @ 2020-01-16 17:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
Hi Tom, and thanks.
Running ANALYZE doesn't change a thing. REINDEXING doesn't change a thing.
I know it's an odd choice of plan - that's why I'm here!
I thought I'd just post what felt relevant, hoping it's not something out
of the ordinary and I'm just missing something obvious.
Here's lots of data:
===========================================================================
SELECT version()
PostgreSQL 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
===========================================================================
\d "LucrareBugetDate"
Table
"public.LucrareBugetDate"
Column | Type | Collation | Nullable
| Default
----------------------------+-----------------------+-----------+----------+------------------------------------------------------------------
OrdonatorPrincipalId | uuid | | |
UnitateSubordonataId | uuid | | |
CentralizatorSelectiv | text | | |
IdRand | character varying(32) | | |
IdColoana | character varying(32) | | |
ClasEc | character varying(32) | | |
CodSector | character varying(4) | | |
CodSursa | character varying(4) | | |
Paragraf | character varying(16) | | |
Venit | character varying(16) | | |
FelValoare | integer | | not null |
Valoare | numeric | | not null |
RangOperator | integer | | not null |
OrdineCalcul | integer | | not null |
ConflictFormuleAlternative | boolean | | not null
| false
Sectiune | integer | | |
RefColoana | text | | |
RefDocument | text | | |
RefLinie | text | | |
SeqModificare | integer | | not null
| 0
LucrareBugetDateId | integer | | not null
| nextval('"LucrareBugetDate_LucrareBugetDateIdV2_seq"'::regclass)
LucrareBugetVersiuneId | smallint | | not null |
CentralizatorSelectivId | uuid | | |
Stil | text | | |
ValoareArhivata | boolean | | |
Indexes:
"PK_LucrareBugetDate" PRIMARY KEY, btree ("LucrareBugetVersiuneId",
"LucrareBugetDateId")
"IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" btree
("LucrareBugetVersiuneId", "LucrareBugetDateId")
Foreign-key constraints:
"FK_LucrareBugetDate_LucrareBugetVersiune_LucrareBugetVersiuneId"
FOREIGN KEY ("LucrareBugetVersiuneId") REFERENCES
"LucrareBugetVersiune"("LucrareBugetVersiuneId") ON DELETE CASCADE
===========================================================================
SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts,
relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE
relname='LucrareBugetDate';
relname | relpages | reltuples | relallvisible | relkind |
relnatts | relhassubclass | reloptions | pg_table_size
------------------+----------+-------------+---------------+---------+----------+----------------+-----------------+---------------
LucrareBugetDate | 2659660 | 4.17124e+07 | 671510 | r |
25 | f | {fillfactor=50} | 21793775616
(1 row)
===========================================================================
Does the table have anything unusual about it?
- contains large objects: NO
- has a large proportion of NULLs in several columns: NO
- receives a large number of UPDATEs or DELETEs regularly: YES - Lots of
UPDATES but no UPDATES to indexed columns. No DELETE's.
- is growing rapidly: I'm inserting millions of records at once but not
very often. Have manually done ANALYZE and REINDEX
- has many indexes on it: NO
- uses triggers that may be executing database functions, or is calling
functions directly: NO
===========================================================================
EXPLAIN (ANALYZE, BUFFERS) select "IdRand", "IdColoana", "Valoare" from
"LucrareBugetDate" where ("LucrareBugetVersiuneId" = 92) and
("LucrareBugetDateId" in (10,11));
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using
"IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" on
"LucrareBugetDate" (cost=0.56..2.37 rows=1 width=13) (actual
time=0.096..978.398 rows=2 loops=1)
Index Cond: ("LucrareBugetVersiuneId" = 92)
Filter: ("LucrareBugetDateId" = ANY ('{10,11}'::integer[]))
Rows Removed by Filter: 1869178
Buffers: shared hit=161178
Planning time: 0.699 ms
Execution time: 978.433 ms
===========================================================================
Was this query always slow, or has it gotten slower over time? If the
plan/execution of the query used to be different, do you have copies of
those query plans? Has anything changed in your database other than the
accumulation of data?
The query is usually instantaneous.
Here's the same query ran o a different server running the same database
with comparable data (COLD server, frist run! The second run has execution
time = 0.040ms):
EXPLAIN (ANALYZE, BUFFERS) select "IdRand", "IdColoana", "Valoare" from
"LucrareBugetDate" where ("LucrareBugetVersiuneId" = 92) and
("LucrareBugetDateId" in (10,11));
Index Scan using "PK_LucrareBugetDate" on "LucrareBugetDate"
(cost=0.56..4.85 rows=2 width=13) (actual time=22.922..23.123 rows=2
loops=1)
Index Cond: (("LucrareBugetVersiuneId" = 92) AND ("LucrareBugetDateId" =
ANY ('{10,11}'::integer[])))
Buffers: shared hit=12 read=4
Planning time: 66.743 ms
Execution time: 23.190 ms
===========================================================================
Hardware:
2 x Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz, 128 GBhz, Local ZFS-based
storage built from 4 x NVME SSD drives.
I doubt it's hardware related.
===========================================================================
SELECT * FROM pg_stat_user_tables WHERE relname='table_name';
relid | schemaname | relname | seq_scan | seq_tup_read |
idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del |
n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze |
last_vacuum | last_autovacuum | last_analyze |
last_autoanalyze | vacuum_count | autovacuum_count |
analyze_count | autoanalyze_count
-------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------------------------+-----------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
20655 | public | LucrareBugetDate | 306 | 7765749768 | 8398680
| 983464378904 | 58388025 | 2944618 | 16675590 | 2887093 |
41712435 | 61524 | 2588381 | 2019-11-03 19:15:58.765546+00
| | 2020-01-15 16:11:26.301756+00 | 2019-12-20
10:12:53.737619+00 | 1 | 0 | 40 |
12
===========================================================================
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV,
tablename, attname, inherited, null_frac, n_distinct,
array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1)
n_hist, correlation FROM pg_stats WHERE attname='LucrareBugetDateId' AND
tablename='LucrareBugetDate' ORDER BY 1 DESC;
frac_mcv | tablename | attname | inherited | null_frac
| n_distinct | n_mcv | n_hist | correlation
------------+------------------+--------------------+-----------+-----------+-------------+-------+--------+-------------
0.00666667 | LucrareBugetDate | LucrareBugetDateId | f | 0
| 2.02985e+06 | 100 | 101 | 0.0631249
===========================================================================
SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV,
tablename, attname, inherited, null_frac, n_distinct,
array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1)
n_hist, correlation FROM pg_stats WHERE attname='LucrareBugetVersiuneId'
AND tablename='LucrareBugetDate' ORDER BY 1 DESC;
frac_mcv | tablename | attname | inherited |
null_frac | n_distinct | n_mcv | n_hist | correlation
----------+------------------+------------------------+-----------+-----------+------------+-------+--------+-------------
1 | LucrareBugetDate | LucrareBugetVersiuneId | f |
0 | 22 | 22 | | 0.624823
(1 row)
===========================================================================
I think I went through most of
https://wiki.postgresql.org/wiki/SlowQueryQuestions
I can provide more information if helpful.
On Thu, 16 Jan 2020 at 17:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Cosmin Prund <cprund@gmail.com> writes:
> > explain analyze
> > select R, C, V from LBD
> > where Ver = 92 and Id in (10,11)
>
> > Index Scan using "IX_LBD_Ver_Id" on "LBD" (cost=0.56..2.37 rows=1
> > width=13) (actual time=0.063..857.725 rows=2 loops=1)
> > Index Cond: ("Ver" = 92)
> > Filter: ("Id" = ANY ('{10,11}'::integer[]))
> > Rows Removed by Filter: 1869178
> > Planning time: 0.170 ms
> > Execution time: 857.767 ms
>
> > The IX_LBD_Ver_Id index is on two columns (Ver, Id) - it's not in "Ver"
> > alone!
>
> Seems like an odd choice of plan, then, but you haven't provided any
> detail that would let anyone guess why it's not using the second index
> column. For starters it would be good to show the exact table and
> index schema (eg via \d+ in psql). Also, does explicitly ANALYZE'ing
> the table change anything?
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 17:18 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
@ 2020-01-16 18:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-01-16 19:06 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2020-01-16 18:20 UTC (permalink / raw)
To: Cosmin Prund <cprund@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, 2020-01-16 at 19:18 +0200, Cosmin Prund wrote:
> Indexes:
> "PK_LucrareBugetDate" PRIMARY KEY, btree ("LucrareBugetVersiuneId", "LucrareBugetDateId")
> "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" btree ("LucrareBugetVersiuneId", "LucrareBugetDateId")
> Foreign-key constraints:
> "FK_LucrareBugetDate_LucrareBugetVersiune_LucrareBugetVersiuneId" FOREIGN KEY ("LucrareBugetVersiuneId") REFERENCES "LucrareBugetVersiune"("LucrareBugetVersiuneId") ON DELETE CASCADE
>
> EXPLAIN (ANALYZE, BUFFERS) select "IdRand", "IdColoana", "Valoare" from "LucrareBugetDate" where ("LucrareBugetVersiuneId" = 92) and ("LucrareBugetDateId" in (10,11));
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" on "LucrareBugetDate" (cost=0.56..2.37 rows=1 width=13) (actual time=0.096..978.398 rows=2 loops=1)
> Index Cond: ("LucrareBugetVersiuneId" = 92)
> Filter: ("LucrareBugetDateId" = ANY ('{10,11}'::integer[]))
> Rows Removed by Filter: 1869178
> Buffers: shared hit=161178
> Planning time: 0.699 ms
> Execution time: 978.433 ms
Well, what should the poor thing do?
There is no index on "LucrareBugetDateId".
Rather, you have two indexes on ("LucrareBugetVersiuneId", "LucrareBugetDateId"),
one of which should be dropped.
Try with an index on "LucrareBugetDateId".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 17:18 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 18:20 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Laurenz Albe <laurenz.albe@cybertec.at>
@ 2020-01-16 19:06 ` Cosmin Prund <cprund@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Cosmin Prund @ 2020-01-16 19:06 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Thu, 16 Jan 2020 at 20:20, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> Well, what should the poor thing do?
> There is no index on "LucrareBugetDateId".
>
I did add an index on "LucrareBugetDateId" (before accidentally "fixing"
the problem with ANALYZE) and it didn't help.
> Rather, you have two indexes on ("LucrareBugetVersiuneId",
> "LucrareBugetDateId"),
> one of which should be dropped.
>
One will be dropped. The second one was added out of desperation (because
it wasn't using the first one).
> Try with an index on "LucrareBugetDateId".
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 17:18 ` Re: Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
@ 2020-01-16 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2020-01-16 18:23 UTC (permalink / raw)
To: Cosmin Prund <cprund@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Cosmin Prund <cprund@gmail.com> writes:
> I know it's an odd choice of plan - that's why I'm here!
Indeed. I cannot reproduce it here on 10.11:
regression=# create table bb(f1 smallint, f2 serial, primary key(f1,f2));
CREATE TABLE
regression=# explain select * from bb where f1 = 92 and f2 in (10,11);
QUERY PLAN
-----------------------------------------------------------------------
Index Only Scan using bb_pkey on bb (cost=0.15..8.34 rows=1 width=6)
Index Cond: ((f1 = 92) AND (f2 = ANY ('{10,11}'::integer[])))
(2 rows)
As I said before, as long as it chooses an indexscan at all, I wouldn't
expect variation in what clauses it chooses to use with the index.
So I don't see why this trivial example doesn't replicate your result.
If you try exactly the above on your database, do you get my result,
or a plan more like yours?
I wonder if you have some extension installed that's causing the
operators to be interpreted differently.
BTW, why do you have two identical indexes on the table?
> Indexes:
> "PK_LucrareBugetDate" PRIMARY KEY, btree ("LucrareBugetVersiuneId",
> "LucrareBugetDateId")
> "IX_LucrareBugetDate_LucrareBugetVersiuneId_LucrareBugetDateId" btree
> ("LucrareBugetVersiuneId", "LucrareBugetDateId")
That shouldn't be affecting this either, but it seems wasteful.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2020-01-16 19:52 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-16 14:06 Bad query plan decision when using multiple column index - postgresql uses only first column then filters Cosmin Prund <cprund@gmail.com>
2020-01-16 15:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 16:59 ` Michael Lewis <mlewis@entrata.com>
2020-01-16 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 18:15 ` Cosmin Prund <cprund@gmail.com>
2020-01-16 18:52 ` Michael Lewis <mlewis@entrata.com>
2020-01-16 18:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-16 19:52 ` Cosmin Prund <cprund@gmail.com>
2020-01-16 17:18 ` Cosmin Prund <cprund@gmail.com>
2020-01-16 18:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-01-16 19:06 ` Cosmin Prund <cprund@gmail.com>
2020-01-16 18:23 ` Tom Lane <tgl@sss.pgh.pa.us>
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