From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Emil Iggland <emil(dot)iggland(at)metrima(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected. |
Date: | 2022-04-25 16:00:21 |
Message-ID: | 87994.1650902421@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Emil Iggland <emil(dot)iggland(at)metrima(dot)com> writes:
> The query that is giving us issues is the following, channel 752433 has
> NO values, 752431 has values.
> (Channel 752433 only has valueseries 752434)
> select * from datavalue
> where dataview in ( select id from valueseries where channel =
> %channel_idx%)
> ORDER BY VALUETIMESTAMP DESC
> FETCH FIRST ROW only;
> Running explain analyze shows strange numbers, 52'000 rows are being
> returned but there are no rows there.
> For channel 752433
> -> Index Scan Backward using
> datavalue_2022_03_valuetimestamp_dataview_idx on datavalue_2022_03
> datavalue_6 (cost=0.42..7166.19 rows=119673 width=226) (actual
> time=0.008..32.831 rows=119601 loops=1)
You've got the wrong column order (for this query anyway) in that
index. It'd work a lot better if dataview were the first column;
or at least, it wouldn't tempt the planner to try this unstably-
performing plan. It's trying to use the index ordering to satisfy
the ORDER BY, which works great as long as it finds a dataview
match in some reasonably recent index entry. Otherwise, it's
going to crawl the whole index to discover that there's no match.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Emil Iggland | 2022-04-26 07:41:52 | Re: Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected. |
Previous Message | Emil Iggland | 2022-04-22 14:53:48 | Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected. |