Automated bottleneck detection

[Thomas Güttler]

This sound good. Looks like an automated bootleneck detection
could be possible with pg_wait_sampling.

Regards,
  Thomas

Am 25.07.2018 um 12:25 schrieb Julien Rouhaud:

On Wed, Jul 25, 2018 at 11:14 AM, Thomas Güttler
 wrote:


AFAIK powa is based on pg_stat_statements not on statistical samples.
But maye I am wrong.


Indeed, it's based on pg_stat_statements, but other extensions are
supported too.  Since pg_stat_statements already provides cumulated
counters, there's no need to do sampling.  But if you're interested in
wait events information for instance, it supports (in development
version) pg_wait_sampling extension, which does sampling to provide
efficient and informative informations.



--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Re: Automated bottleneck detection

[MichaelDBA]

Wow, freakin cool, can't wait to start fiddling with pg_wait_sampling.  
Reckon we can get lightweight locks and spinlocks history with this cool 
new extension instead of awkwardly and repeatedly querying the 
pg_stat_activity table.


Regards,
Michael Vitale


Thomas Güttler 
Thursday, July 26, 2018 7:27 AM
This sound good. Looks like an automated bootleneck detection
could be possible with pg_wait_sampling.

Regards,
  Thomas

Query with "ILIKE ALL" does not use the index

[Nicolas Even]

Hi,

I have the following table:

 Table "public.totoz"
  Column   |   Type   | Collation | Nullable | Default
---+--+---+--+-
 name  | character varying(512)   |   | not null |
Indexes:
"totoz_pkey" PRIMARY KEY, btree (name)
"totoz_name_trgrm_idx" gin (name gin_trgm_ops)



When I run the following query, it uses the totoz_name_trgrm_idx as expected:

explain analyze select name from totoz where name ilike '%tot%';
  QUERY PLAN
---
 Bitmap Heap Scan on totoz  (cost=48.02..59.69 rows=3 width=11)
(actual time=0.205..0.446 rows=88 loops=1)
   Recheck Cond: ((name)::text ~~* '%tot%'::text)
   Heap Blocks: exact=85
   ->  Bitmap Index Scan on totoz_name_trgrm_idx  (cost=0.00..48.02
rows=3 width=0) (actual time=0.177..0.177 rows=88 loops=1)
 Index Cond: ((name)::text ~~* '%tot%'::text)
 Planning time: 0.302 ms
 Execution time: 0.486 ms
(7 rows)



However when I run the same (as far as I understand it) query but with
the ALL operator, the index is not used:

explain analyze select name from totoz where name ilike all(array['%tot%']);
 QUERY PLAN
-
 Index Only Scan using totoz_pkey on totoz  (cost=0.29..1843.64 rows=3
width=11) (actual time=3.854..20.757 rows=88 loops=1)
   Filter: ((name)::text ~~* ALL ('{%tot%}'::text[]))
   Rows Removed by Filter: 30525
   Heap Fetches: 132
 Planning time: 0.230 ms
 Execution time: 20.778 ms
(6 rows)


I'd have expected the second query to use the totoz_name_trgrm_idx but
it doesn't. Why is that?

Thanks for your help!

Re: Query with "ILIKE ALL" does not use the index

[Tom Lane]

Nicolas Even  writes:
> However when I run the same (as far as I understand it) query but with
> the ALL operator, the index is not used:
> explain analyze select name from totoz where name ilike all(array['%tot%']);

There's only index support for "op ANY (array)", not "op ALL (array)".

regards, tom lane

Re: Query with "ILIKE ALL" does not use the index

[Matthew Hall]

On Jul 26, 2018, at 9:44 AM, Tom Lane  wrote:
> 
> Nicolas Even  writes:
>> However when I run the same (as far as I understand it) query but with
>> the ALL operator, the index is not used:
>> explain analyze select name from totoz where name ilike all(array['%tot%']);
> 
> There's only index support for "op ANY (array)", not "op ALL (array)".
> 
>   regards, tom lane

Nicolas,

Could you work around the limitation with a two-clause WHERE?

First clause ANY, second clause ALL.

I've done some similar tricks on similar sorts of queries.

Matthew.

Re: Query with "ILIKE ALL" does not use the index

[Nicolas Even]

Hi Matthew,

I finally used "WHERE name ILIKE arr[1] AND name ILIKE ALL(arr)" which
works well enough for my use case.

Thank you
Nicolas

On 26 July 2018 at 19:22, Matthew Hall  wrote:
> On Jul 26, 2018, at 9:44 AM, Tom Lane  wrote:
>>
>> Nicolas Even  writes:
>>> However when I run the same (as far as I understand it) query but with
>>> the ALL operator, the index is not used:
>>> explain analyze select name from totoz where name ilike all(array['%tot%']);
>>
>> There's only index support for "op ANY (array)", not "op ALL (array)".
>>
>>   regards, tom lane
>
> Nicolas,
>
> Could you work around the limitation with a two-clause WHERE?
>
> First clause ANY, second clause ALL.
>
> I've done some similar tricks on similar sorts of queries.
>
> Matthew.

Re: Query with "ILIKE ALL" does not use the index

[Nicolas Even]

Thank you Tom

On 26 July 2018 at 18:44, Tom Lane  wrote:
> Nicolas Even  writes:
>> However when I run the same (as far as I understand it) query but with
>> the ALL operator, the index is not used:
>> explain analyze select name from totoz where name ilike all(array['%tot%']);
>
> There's only index support for "op ANY (array)", not "op ALL (array)".
>
> regards, tom lane