Not really, the server has 2 GB memory (PROD is a lot more than this dev box),
so the table should be able to fit in memory if we preload them.
MemTotal:2049572 kB
dev=# select pg_size_pretty(pg_relation_size('data'));
pg_size_pretty
141 MB
(1 row)
Time: 2.640 ms
dev
Suya Huang writes:
> Thank you Tom very much, thatâs the piece of information I miss.
> So, should I expect that the nested loop join would be much faster if I cache
> both tables (use pg_prewarm) into memory as it waives the disk read?
pg_prewarm is not going to magically fix things if your
Hi Claudio,
Here is the index definition
"idx_data_3" gin (name gin_trgm_ops), tablespace "tbs_data"
"idx_data_4" gin (displayname gin_trgm_ops), tablespace "tbs_data"
On 8/10/16, 10:49 AM, "Claudio Freire" wrote:
On Tue, Aug 9, 2016 at 9:46 PM, Claudio Freire wrote:
> On Tue, Aug 9, 2
Thank you Tom very much, that’s the piece of information I miss.
So, should I expect that the nested loop join would be much faster if I cache
both tables (use pg_prewarm) into memory as it waives the disk read?
Thanks,
Suya
On 8/10/16, 10:45 AM, "Tom Lane" wrote:
Suya Huang writes:
>
On Tue, Aug 9, 2016 at 9:46 PM, Claudio Freire wrote:
> On Tue, Aug 9, 2016 at 9:34 PM, Suya Huang wrote:
>> dev=# explain analyze
>> SELECT COALESCE(w.displayname, o.name) FROM order o INNER JOIN data w
>> ON w.name = o.name WHERE (w.name LIKE '%dog%' OR w.displayname LIKE
>> '%dog%') AND
On Tue, Aug 9, 2016 at 9:34 PM, Suya Huang wrote:
> dev=# explain analyze
> SELECT COALESCE(w.displayname, o.name) FROM order o INNER JOIN data w
> ON w.name = o.name WHERE (w.name LIKE '%dog%' OR w.displayname LIKE
> '%dog%') AND (NOT w.categories && ARRAY[1, 6, 10, 1337])
> ORDER BY
Suya Huang writes:
>-> Index Scan using idx_order_1_us on order o
> (cost=0.56..8.58 rows=1 width=30) (actual time=5.814..5.814 rows=0 loops=526)
4 or so ms per row fetched is well within expectation for random access to
spinning-rust media. For example, a 15K RPM drive spins
Hi Claudio,
here comes the dog version:
dev=# explain analyze
dev-# SELECT COALESCE(w.displayname, o.name) FROM order o INNER JOIN data w
dev-# ON w.name = o.name WHERE (w.name LIKE '%dog%' OR w.displayname LIKE
'%dog%') AND (NOT w.categories && ARRAY[1, 6, 10, 1337])
dev-# ORDER BY o.cnt DESC L
On Tue, Aug 9, 2016 at 9:12 PM, Suya Huang wrote:
> Hi Claudio,
>
> The plan for dog is exactly the same as what’s for cat, thus I didn’t paste
> them here.
Are you sure?
The plan itself may be the same, but the numbers may be different, and
in fact be key to understanding the problem.
>
> Ric
Hi Claudio,
The plan for dog is exactly the same as what’s for cat, thus I didn’t paste
them here.
Richard Albright just pointed that it’s because the result has been cached not
the table, I think that makes sense. So my question changes to the efficiency
of NESTED LOOP JOIN, 400 rows for 4 se
On Tue, Aug 9, 2016 at 8:27 PM, Suya Huang wrote:
> I’ve got a SQL runs for about 4 seconds first time it’s been executed,but
> very fast (20ms) for the consequent runs. I thought it’s because that the
> first time table being loaded into memory. However, if you change the where
> clause value fro
Hi,
I’ve got a SQL runs for about 4 seconds first time it’s been executed,but very
fast (20ms) for the consequent runs. I thought it’s because that the first time
table being loaded into memory. However, if you change the where clause value
from “cat” to “dog”, it runs about 4 seconds as it’s n
12 matches
Mail list logo
