On 2016-02-01 12:35:35 -0600, Yu Nie wrote:
> Recently I am working with a large amount of taxis GIS data and had
> encountered
> some weird performance issues. I am hoping someone in this community can help
> me figure it out.
>
> The taxi data were loaded in 5 minute block into a table. I hav
please stop top posting, and quoting 100s and 100s of lines of old
dreck.This list uses inline posting, and its preferred to edit out
any unimportant junk from the quoted postings.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postg
13_01w has not been clustered?
>>>> If you cluster data2013_01w on the index, does the performance change?
>>>>
>>>> On Mon, Feb 1, 2016 at 4:03 PM, Yu Nie wrote:
>>>>
>>>>> Melvin,
>>>>>
>>>>> Please see a
;> Melvin,
>>>>
>>>> Please see attached for the requests results. I ran two queries (each
>>>> with a different taxiid that is next to each other) for each table. Note
>>>> that for the large table one is much faster than the other because the
>
gt;>
>>>> Many thanks for your willingness to help!
>>>>
>>>> Best, Marco
>>>>
>>>> On Mon, Feb 1, 2016 at 2:25 PM, melvin6925
>>>> wrote:
>>>>
>>>>> Fine. Please rerun both explains a
k however for
>>>> the small table.
>>>>
>>>> Many thanks for your willingness to help!
>>>>
>>>> Best, Marco
>>>>
>>>> On Mon, Feb 1, 2016 at 2:25 PM, melvin6925
>>>> wrote:
>>>>
>>>>> Fine. Pl
o a
>> file (F8 in PGADMIN Sql). Then attach the file.
>>
>>
>>
>> Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone
>> Original message
>> From: Yu Nie
>> Date: 2/1/2016 15:17 (GMT-05:00)
>> To: melvin6925
Galaxy S® 6, an AT&T 4G LTE smartphone
> Original message
> From: Yu Nie
> Date: 2/1/2016 15:17 (GMT-05:00)
> To: melvin6925
> Subject: Re: [GENERAL] strange sql behavior
>
> Yes, absolutely.
>
> On Mon, Feb 1, 2016 at 2:12 PM, melvin6925 wrote:
&g
Hi Klaver,
Thanks.
1. I don't see order by time makes a difference - in fact, the "analyze"
seems to indicate the sorting is faster for the small table because it uses
less memory.
2. No, the large table has not been clustered. Both tables were created
exactly the same way, loading 5-minute b
On 02/01/2016 10:35 AM, Yu Nie wrote:
Hi there,
Recently I am working with a large amount of taxis GIS data and had
encountered some weird performance issues. I am hoping someone in this
community can help me figure it out.
The taxi data were loaded in 5 minute block into a table. I have two
eral@postgresql.org Subject: Re: [GENERAL]
strange sql behavior
Thanks, Bill and Melvin!
Just some quick note/answers before I absorb all the information provided by
Bill.
1. I don't expect many users running queries against the tables, especially for
the small table - since I just created it thi
45 PM, melvin6925 wrote:
> Thanks Bill.
> Also, it's very important to include the headers with the queries!
>
> Marco,
> There is no top secret information that is requested, so please do not
> edit the output.
>
>
>
> Sent via the Samsung Galaxy S® 6, an AT&
Date: 2/1/2016
14:41 (GMT-05:00) To: Yu Nie Cc: Melvin Davidson
, pgsql-general@postgresql.org Subject: Re: [GENERAL]
strange sql behavior
Came a little late to the thread, see many comments inline below:
On Mon, 1 Feb 2016 13:16:13 -0600
Yu Nie wrote:
> Thanks a lot for your repl
Came a little late to the thread, see many comments inline below:
On Mon, 1 Feb 2016 13:16:13 -0600
Yu Nie wrote:
> Thanks a lot for your reply. I ran the query you suggested and here are
> the results
>
> Large table: "public";"data2011_01";784236885;0;0;"";"";"";"2016-01-19
> 17:31:08-06";1
Hi Melvin,
Thanks a lot for your reply. I ran the query you suggested and here are
the results
Large table: "public";"data2011_01";784236885;0;0;"";"";"";"2016-01-19
17:31:08-06";156847423
Small table: "public";"data2013_01w";300786444;0;0;"";"";"2016-02-01
08:57:24-06";"2016-02-01 04:01:04-06"
One thing to look at is the last time both tables were vacuumed/analyzed.
SELECT n.nspname,
s.relname,
c.reltuples::bigint,
n_tup_ins,
n_tup_upd,
n_tup_del,
date_trunc('second', last_vacuum) as last_vacuum,
date_trunc('second', last_autovacuum) as l
Hi there,
Recently I am working with a large amount of taxis GIS data and had
encountered some weird performance issues. I am hoping someone in this
community can help me figure it out.
The taxi data were loaded in 5 minute block into a table. I have two
separate such tables, one stores a month
17 matches
Mail list logo
