Re: [GENERAL] Fetching last n records from Posgresql
Hi Pushkar: On Wed, Mar 30, 2016 at 8:40 AM, Deole, Pushkar (Pushkar) wrote: > Does PostgreSQL support a query to fetch last ‘n’ records that match the > selection criteria. I am trying to fetch records from a table with start > date that falls in last 30 days, however, I want to fetch the oldest ‘n’ > records and not the recent ones. I know there is a LIMIT clause which I can > use but it will fetch the first ‘n’ records. > > I came across an approach which says that I can reverse the order and then > use LIMIT and then order the records back using timestamp as below, but > looking at the execution plan, it has to do a sort twice which may affect > the performance of query if ‘n’ is large number: To get at the last N records you generally have to approaches, read all of them Do you have indexes on the record date? Because in this case it seems that could be solved by a reverse index scan, In my case with a somehow big table: $ \d carrier_cdrs_201603 Table "public.carrier_cdrs_201603" Column | Type | Modifiers -+--+--- ... setup | timestamp with time zone | ... Indexes: "idx_carrier_cdrs_201603_setup" btree (setup) ... $ explain select * from carrier_cdrs_201603 order by setup desc limit 1000; QUERY PLAN -- Limit (cost=0.42..46.25 rows=1000 width=81) -> Index Scan Backward using idx_carrier_cdrs_201603_setup on carrier_cdrs_201603 (cost=0.42..33585.03 rows=732883 width=81) (2 rows) $ explain select * from ( select * from carrier_cdrs_201603 order by setup desc limit 1000 ) last_1000 order by setup; QUERY PLAN Sort (cost=106.08..108.58 rows=1000 width=81) Sort Key: carrier_cdrs_201603.setup -> Limit (cost=0.42..46.25 rows=1000 width=81) -> Index Scan Backward using idx_carrier_cdrs_201603_setup on carrier_cdrs_201603 (cost=0.42..33585.03 rows=732883 width=81) (4 rows) $ explain with last_1000 as ( select * from carrier_cdrs_201603 order by setup desc limit 1000 ) select * from last_1000 order by setup; QUERY PLAN -- Sort (cost=116.08..118.58 rows=1000 width=184) Sort Key: last_1000.setup CTE last_1000 -> Limit (cost=0.42..46.25 rows=1000 width=81) -> Index Scan Backward using idx_carrier_cdrs_201603_setup on carrier_cdrs_201603 (cost=0.42..33585.03 rows=732883 width=81) -> CTE Scan on last_1000 (cost=0.00..20.00 rows=1000 width=184) (6 rows) The faster for me seems to be the subquery way, with timings and usaing 10k records it says: $ explain analyze select * from ( select * from carrier_cdrs_201603 order by setup desc limit 1 ) last_1 order by setup; QUERY PLAN -- Sort (cost=1223.09..1248.09 rows=1 width=81) (actual time=29.646..35.780 rows=1 loops=1) Sort Key: carrier_cdrs_201603.setup Sort Method: quicksort Memory: 1791kB -> Limit (cost=0.42..458.70 rows=1 width=81) (actual time=0.015..20.707 rows=1 loops=1) -> Index Scan Backward using idx_carrier_cdrs_201603_setup on carrier_cdrs_201603 (cost=0.42..33627.38 rows=733773 width=81) (actual time=0.013..8.835 rows=1 loops=1) Total runtime: 41.913 ms (6 rows) And I fear its scanning and feeding into the sort, and accounting for a part of the scan time in the sort phase as just the inner query gives: $ explain analyze select * from carrier_cdrs_201603 order by setup desc limit 1 ; QUERY PLAN Limit (cost=0.42..458.70 rows=1 width=81) (actual time=0.015..20.938 rows=1 loops=1) -> Index Scan Backward using idx_carrier_cdrs_201603_setup on carrier_cdrs_201603 (cost=0.42..33627.38 rows=733773 width=81) (actual time=0.013..8.803 rows=1 loops=1) Total runtime: 27.020 ms (3 rows) So, 14 ms to sort 10k records seems like a reasonable price to pay. As you see, only one sort, in whichever order I do it, and postgres sorts really fast. This is very difficult to avoid. A smarter optimizer could turn the sort into a reverse, but it seems difficult. Or you could try to use a cursor, goto to the
Re: [GENERAL] Fetching last n records from Posgresql
On 30 March 2016 at 11:36, Deole, Pushkar (Pushkar) wrote: > I am sorry I didn’t clarify my requirement properly.. I want the ‘n’ > oldest records, however, they should sorted with the recent record first > and I want this to happen in the query itself so I don’t have to care about > sorting through the application.. > > > > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *Moreno Andreo > *Sent:* Wednesday, March 30, 2016 3:03 PM > *To:* pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] Fetching last n records from Posgresql > > > > Il 30/03/2016 11:19, Deole, Pushkar (Pushkar) ha scritto: > > select * from t order by record_date desc limit 5; > > > > this will return the recent 5 records.. what I want is the oldest 5 > records (in last 30 days) > > so remove "desc", in order to have ascending ordering, thus first 5 > records are the five oldest: > > select * from t order by record_date limit 5 > > Cheers > Moreno.- > > Slight modification then... select * from (select * from t where record_date>now()-'30 day'::interval order by record_date limit 5) as t order by record_date desc; Regards, Sándor Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről küldték. www.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
Re: [GENERAL] Fetching last n records from Posgresql
Il 30/03/2016 11:36, Deole, Pushkar (Pushkar) ha scritto: I am sorry I didn’t clarify my requirement properly.. I want the ‘n’ oldest records, however, they should sorted with the recent record first and I want this to happen in the query itself so I don’t have to care about sorting through the application.. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Moreno Andreo Sent: Wednesday, March 30, 2016 3:03 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Fetching last n records from Posgresql Il 30/03/2016 11:19, Deole, Pushkar (Pushkar) ha scritto: select * from t order by record_date desc limit 5; this will return the recent 5 records.. what I want is the oldest 5 records (in last 30 days) so remove "desc", in order to have ascending ordering, thus first 5 records are the five oldest: select * from t order by record_date limit 5 Cheers Moreno.- select * from (select * from t where record_date >=current_date() - '30 days' order by record_date limit 5) order by record_date desc Not tested, but should work... Cheers Moreno.-
Re: [GENERAL] Fetching last n records from Posgresql
I am sorry I didn’t clarify my requirement properly.. I want the ‘n’ oldest records, however, they should sorted with the recent record first and I want this to happen in the query itself so I don’t have to care about sorting through the application.. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Moreno Andreo Sent: Wednesday, March 30, 2016 3:03 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Fetching last n records from Posgresql Il 30/03/2016 11:19, Deole, Pushkar (Pushkar) ha scritto: select * from t order by record_date desc limit 5; this will return the recent 5 records.. what I want is the oldest 5 records (in last 30 days) so remove "desc", in order to have ascending ordering, thus first 5 records are the five oldest: select * from t order by record_date limit 5 Cheers Moreno.-
Re: [GENERAL] Fetching last n records from Posgresql
Il 30/03/2016 11:19, Deole, Pushkar (Pushkar) ha scritto: select * from t order by record_date desc limit 5; this will return the recent 5 records.. what I want is the oldest 5 records (in last 30 days) so remove "desc", in order to have ascending ordering, thus first 5 records are the five oldest: select * from t order by record_date limit 5 Cheers Moreno.-
Re: [GENERAL] Fetching last n records from Posgresql
On 30 March 2016 at 11:19, Deole, Pushkar (Pushkar) wrote: > select * from t order by record_date desc limit 5; > > > > this will return the recent 5 records.. what I want is the oldest 5 > records (in last 30 days) > > > > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *John R Pierce > *Sent:* Wednesday, March 30, 2016 12:38 PM > *To:* pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] Fetching last n records from Posgresql > > > > On 3/29/2016 11:40 PM, Deole, Pushkar (Pushkar) wrote: > > > > WITH t AS ( > > SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 > > ) > > SELECT * FROM t ORDER BY record_date DESC; > > > > > why do it twice when you can just do > > > > select * from t order by record_date desc limit 5; > -- > > john r pierce, recycling bits in santa cruz > > Then: select * from t where record_date>now()-'30 day'::interval order by record_date limit 5; Regards, Sándor Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről küldték. www.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
Re: [GENERAL] Fetching last n records from Posgresql
select * from t order by record_date desc limit 5; this will return the recent 5 records.. what I want is the oldest 5 records (in last 30 days) From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce Sent: Wednesday, March 30, 2016 12:38 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Fetching last n records from Posgresql On 3/29/2016 11:40 PM, Deole, Pushkar (Pushkar) wrote: WITH t AS ( SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 ) SELECT * FROM t ORDER BY record_date DESC; why do it twice when you can just do select * from t order by record_date desc limit 5; -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Fetching last n records from Posgresql
On 3/29/2016 11:40 PM, Deole, Pushkar (Pushkar) wrote: WITH t AS ( SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 ) SELECT * FROM t ORDER BY record_date DESC; why do it twice when you can just do select * from t order by record_date desc limit 5; -- john r pierce, recycling bits in santa cruz
[GENERAL] Fetching last n records from Posgresql
Hi, Does PostgreSQL support a query to fetch last 'n' records that match the selection criteria. I am trying to fetch records from a table with start date that falls in last 30 days, however, I want to fetch the oldest 'n' records and not the recent ones. I know there is a LIMIT clause which I can use but it will fetch the first 'n' records. I came across an approach which says that I can reverse the order and then use LIMIT and then order the records back using timestamp as below, but looking at the execution plan, it has to do a sort twice which may affect the performance of query if 'n' is large number: WITH t AS ( SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5 ) SELECT * FROM t ORDER BY record_date DESC; Any thoughts/opinions? Thanks, Pushkar
