Not sure how you select the household
>
> The result is (only the first column is shown):
>
> household_name
>
> "Garcia"
> "Armstrong"
> "Armstrong"
> "Bauer"
> "Bauer"
> "Berst"
> "Berst"
> "Minch ()"
> "Berst"
> “Besel”
but unless you select from the resulting table using again an orde
> On 25 Jan 2021, at 20:17, James B. Byrne wrote:
>
> I am recreating a test database with data provided for a series of future
> exercises. Presently I am trying to understand why a simple insert statement
> is not working. The user invoking this insert is 'postgres' and has superuser
> priv
> Good luck!
Tx! And tx for your support.
>
> Ahhh. You don't have a single column index on the timestamp value or a multi
> column one with timestamp first. No wonder the subquery didn't help. My
> apologies for not realizing that before. Thanks for satisfying my curiosity
> why it didn't perform like it should. Certainly, that index
> Curious, how accurate is that row count of 1.2 million records for 3 days?
Not to bad actually
select count(mv_inner.*)
from measurement_value AS mv_inner
where mv_inner.timestamp > '2020-11-06'::timestamp - interval '1 day' and
mv_inner.timestamp < '2020-11-07'::timestamp + interval '1 day'
>
> Curious, what is seq_page_cost and random_page_cost?
show seq_page_cost
->1
show random_page_cost
->4
> Any idea of your cache hits for indexes?
No, I am afraid not. It’s been a long time since I went that deep in the RDBMS
behaviour (must have been Oracle 7.something :) )
> If they a
lent. I
guess I was wrong :). Wonder how stable this plan will be though
Peter
> On 10 Nov 2020, at 09:06, Peter Coppens wrote:
>
> Pavel
>
> Tx for the tip. But given that if the I64_01 column is removed from the
> select list, the index is used I guess the cast is not likely
t; > ('2020-11-06
00:00:00'::timestamp without time zone - pg_timezone_names.utc_offset)) AND
("timestamp" < ('2020-11-07 00:00:00'::timestamp without time zone -
pg_timezone_names.utc_offset)))
Peter
> On 10 Nov 2020, at 08:25, Pavel Stehule wrote:
>
>
>
> út 1
-> Hash (cost=46.83..46.83 rows=683 width=20)
-> Seq Scan on device d (cost=0.00..46.83 rows=683
width=20)
-> Hash (cost=10.00..10.00 rows=1000 width=48)
-> Function Scan on pg_timezone_names (cost=0.00..10.00
Hello,
Consider the following PostgreSQL 9.6.18 tables
- measurement_value: time series table with a unique key on
(device_id,timestamp) columns and a number of columns with measurements.
Table contains a large number of rows (>150million)
- device table: with device properties (short_id joins to
10 matches
Mail list logo
