Re: Why Postgres doesn't use TID scan?

[Rick Otten]

On Wed, Dec 19, 2018 at 6:45 PM Vladimir Ryabtsev 
wrote:

> > The fundamental issue is that "ANY" has two meanings in PG, one of them
> following the SQL standard and one not:
>
> Oh yes, I was aware about two forms but it did not come into my mind, I
> was thinking I use the same form in both cases since my query returns only
> one row and column.
> Thanks for pointing me into that.
>
> --
> Vlad
>

For what it is worth, I have found that if I am checking for the presence
of an object in an array, while this syntax is easy to understand and more
intuitive to craft:

select
  *
from
  mytable
where
  ' test' = ANY (my_varchar_array_column)
;

This syntax is almost always much faster:

select
*
from
mytable
where
  ARRAY['test'::varchar] <@ my_varchar_array_column
;

(Since this is a performance list after all.)

Re: pgbench results arent accurate

[Merlin Moncure]

On Wed, Dec 12, 2018 at 6:54 AM Mariel Cherkassky <
mariel.cherkas...@gmail.com> wrote:

> Hey,
> I installed a new postgres 9.6 on both of my machines. I'm trying to
> measure the differences between the performances in each machine but it
> seems that the results arent accurate.
> I did 2 tests :
>

Better phrased, I'd say the results aren't _stable_ -- 'inaccurate'
suggests that pgbench is giving erroneous results; you've provided no
evidence of that.

Storage performance can seem random; there are numerous complex processes
and caching that are involved between the software layer and the storage.
Some are within the database, some are within the underlying operating
system, and some are within the storage itself.  Spinning media is also
notoriously capricious, various hard to control for factors (such as where
the data precisely exists on the platter) can influence data seek and fetch
times.

I think we can look ahead to a not too distant future where storage
performance will be less important with regards to typical database
performance than it is today.  Clever people that are willing and able to
buy appropriate hardware already live in this world essentially, but the
enterprise storage industry seems strongly inclined to postpone this day of
reckoning as long as possible for obviously selfish reasons.


merlin

>