pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance of SELECT * much faster than SELECT <colname> with large offset
3+ messages / 3 participants
[nested] [flat]
* performance of SELECT * much faster than SELECT <colname> with large offset
@ 2014-10-03 02:17 Marc Slemko <marcs@znep.com>
2014-10-03 02:39 ` Re: performance of SELECT * much faster than SELECT <colname> with large offset Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Marc Slemko @ 2014-10-03 02:17 UTC (permalink / raw)
To: pgsql-performance
I ran into this oddity lately that goes against everything I thought I
understood and was wondering if anyone had any insight. Version/env
details at the end.
The root of it is these query times:
marcs=# select * from ccrimes offset 5140000 limit 1;
[...data omitted...]
(1 row)
Time: 650.280 ms
marcs=# select description from ccrimes offset 5140000 limit 1;
description
-------------------------------------
FINANCIAL IDENTITY THEFT OVER $ 300
(1 row)
Time: 1298.672 ms
These times are all from data that is cached and are very repeatable.
Yes, I know that offset and limit without an order by isn't useful for
paging through data.
And an explain on them both... everything looks the same other than
the width and actual times:
marcs=# explain (analyze,buffers) select * from ccrimes offset 5140000 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=204146.73..204146.73 rows=1 width=202) (actual
time=1067.901..1067.901 rows=1 loops=1)
Buffers: shared hit=152743
-> Seq Scan on ccrimes (cost=0.00..204146.73 rows=5139873
width=202) (actual time=0.014..810.672 rows=5140001 loops=1)
Buffers: shared hit=152743
Total runtime: 1067.951 ms
(5 rows)
Time: 1068.612 ms
marcs=# explain (analyze,buffers) select description from ccrimes
offset 5140000 limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=204146.73..204146.73 rows=1 width=17) (actual
time=1713.027..1713.027 rows=1 loops=1)
Buffers: shared hit=152743
-> Seq Scan on ccrimes (cost=0.00..204146.73 rows=5139873
width=17) (actual time=0.013..1457.521 rows=5140001 loops=1)
Buffers: shared hit=152743
Total runtime: 1713.053 ms
(5 rows)
Time: 1713.612 ms
When I run the query and capture a profile using "perf" and compare
the two, the thing that stands out is the slot_getsomeattrs call that
dominates the trace in the slow query but not in the faster "SELECT *"
version:
- 39.25% postgres postgres [.] _start
- _start
- 99.47% slot_getsomeattrs
ExecProject
ExecScan
ExecProcNode
ExecLimit
ExecProcNode
standard_ExecutorRun
0x7f4315f7c427
PortalRun
PostgresMain
PostmasterMain
main
__libc_start_main
+ 0.53% ExecProject
+ 18.82% postgres postgres [.] HeapTupleSatisfiesMVCC
+ 12.01% postgres postgres [.] 0xb6353
+ 9.47% postgres postgres [.] ExecProject
The table is defined as:
Column | Type | Modifiers
----------------------+--------------------------------+-----------
s_updated_at_0 | timestamp(3) with time zone |
s_version_1 | bigint |
s_id_2 | bigint |
s_created_at_3 | timestamp(3) with time zone |
id | numeric |
case_number | text |
date | timestamp(3) without time zone |
block | text |
iucr | text |
primary_type | text |
description | text |
location_description | text |
arrest | boolean |
domestic | boolean |
beat | text |
district | text |
ward | numeric |
community_area | text |
fbi_code | text |
x_coordinate | numeric |
y_coordinate | numeric |
year | numeric |
updated_on | timestamp(3) without time zone |
latitude | numeric |
longitude | numeric |
location_lat | double precision |
location_long | double precision |
I've been testing this against Postgres 9.3.5 on Ubuntu 12.04 LTS
running with a 3.2.0 kernel, and get similar results on both raw
hardware and in Azure VMs. This repros on boxes with no other load.
Any suggestions about what is going on or where to dig further would
be appreciated. I can make a pgdump of the data I'm using if anyone
is interested.
Thanks.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: performance of SELECT * much faster than SELECT <colname> with large offset
2014-10-03 02:17 performance of SELECT * much faster than SELECT <colname> with large offset Marc Slemko <marcs@znep.com>
@ 2014-10-03 02:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-03 08:40 ` Re: performance of SELECT * much faster than SELECT <colname> with large offset Marti Raudsepp <marti@juffo.org>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2014-10-03 02:39 UTC (permalink / raw)
To: Marc Slemko <marcs@znep.com>; +Cc: pgsql-performance
Marc Slemko <marcs@znep.com> writes:
> I ran into this oddity lately that goes against everything I thought I
> understood and was wondering if anyone had any insight.
SELECT * avoids a projection step ... see ExecAssignScanProjectionInfo.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: performance of SELECT * much faster than SELECT <colname> with large offset
2014-10-03 02:17 performance of SELECT * much faster than SELECT <colname> with large offset Marc Slemko <marcs@znep.com>
2014-10-03 02:39 ` Re: performance of SELECT * much faster than SELECT <colname> with large offset Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-10-03 08:40 ` Marti Raudsepp <marti@juffo.org>
0 siblings, 0 replies; 3+ messages in thread
From: Marti Raudsepp @ 2014-10-03 08:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marc Slemko <marcs@znep.com>; pgsql-performance
On Fri, Oct 3, 2014 at 5:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Marc Slemko <marcs@znep.com> writes:
>> I ran into this oddity lately that goes against everything I thought I
>> understood and was wondering if anyone had any insight.
>
> SELECT * avoids a projection step ... see ExecAssignScanProjectionInfo.
It would be cool if OFFSET could somehow signal the child nodes "don't
bother constructing the actual tuple". Not sure if that could work in
more complex queries. But this is just one of many performance
problems with large OFFSETs.
Of course you can always work around this using a subquery...
select description from (
select * from ccrimes offset 5140000 limit 1
) subq;
But most of the time it's better to use scalable paging techniques:
http://use-the-index-luke.com/sql/partial-results/fetch-next-page
Regards,
Marti
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-10-03 08:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-03 02:17 performance of SELECT * much faster than SELECT <colname> with large offset Marc Slemko <marcs@znep.com>
2014-10-03 02:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-03 08:40 ` Marti Raudsepp <marti@juffo.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox