From: Björn Wittich <Bjoern_Wittich@gmx.de>
To: pgsql-performance@postgresql.org
Subject: Re: extremly bad select performance on huge table
Date: Wed, 22 Oct 2014 07:05:50 +0200
Message-ID: <54473B2E.2080007@gmx.de> (raw)
In-Reply-To: <A76B25F2823E954C9E45E32FA49D70ECAB2FDEE5@mail.corp.perceptron.com>
References: <5446972B.9040409@gmx.de>
<9706.1413918462@sss.pgh.pa.us>
<5446B4B3.4030802@gmx.de>
<A76B25F2823E954C9E45E32FA49D70ECAB2FDEE5@mail.corp.perceptron.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Igor,
that was also my assumption, but unfortunately this isn't true.
I am using the explain analyze.
Example which is fast "explain analyze select value from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"
130 - 140 sec
Example which is fast "explain analyze select value,c1 from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"
does not complete after several hours although the c1 coulmn should only
be relevant for retrieval.
Comparing the explain comparison of both statements gave me a hint:
adding the c1 column changes the query planner to make a sequential scan
on myhugetable as well as on smallertable. This is much slower.
When I set enable_seqscan=false the queryplanner shows the same query
plan for both statements but the statement including the c1 column does
not complete after several hours.
How can this be explained?
I do not want the db server to prepare the whole query result at once,
my intention is that the asynchronous retrieval starts as fast as possible.
Thanks
Björn
>
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Björn Wittich
> Sent: Tuesday, October 21, 2014 3:32 PM
> To: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] extremly bad select performance on huge table
>
> Hi Tom and Igor,
>
> thanks for your help. With the reindex the select query running time was reduced from 5200 sec to 130 sec. Impressive!
>
> Even a join on this table is now fast.
>
> Unfortunately, there is now another problem: The table in my example has
> 500 columns which I want to retrieve with my join command.
>
> Example which is fast "select value from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"
>
> Example which is slow "select value,c1,c2,c3,...,c10 from smallertable inner join myhugetable ON smallertable.mycolumn = myhugetable.mycolumn"
>
>
> Which is the number of columns to fetch so bad ? Which action is done in
> the db system when querying this via pgadmin? I think that there is no
> real retrieval included, why is the number of additional columns so bad
> for the join performance?
>
>> =?ISO-8859-15?Q?Bj=F6rn_Wittich?= <Bjoern_Wittich@gmx.de> writes:
>>> Here is the explain (analyze,buffers) select mycolumn from myhugetable
>>> "Index Only Scan using myprimkey on myhugetable (cost=0.00..8224444.82
>>> rows=71768080 width=33) (actual time=16.722..2456300.778 rows=71825999
>>> loops=1)"
>>> " Heap Fetches: 356861"
>>> " Buffers: shared hit=71799472 read=613813"
>>> "Total runtime: 2503009.611 ms"
>> So that works out to about 4 msec per page fetched considering only I/O
>> costs, which is about as good as you're likely to get if the data is
>> sitting on spinning rust.
>>
>> You could potentially make it faster with a VACUUM (to mark all pages
>> all-visible and eliminate the "heap fetches" costs), or a REINDEX
>> (so that the index scan becomes more nearly sequential instead of random
>> access). However, unless the data is nearly static those will just be
>> temporary fixes: the time will degrade again as you update the table.
>>
>>> Note: This select is just for testing. My final statement will be a join
>>> on this table via the "mycolumn" column.
>> In that case it's probably a waste of time to worry about the performance
>> of this query as such. In the first place, a join is not likely to use
>> the index at all unless it's fetching a relatively small number of rows,
>> and in the second place it seems unlikely that the join query can use
>> an IndexOnlyScan on this index --- I imagine that the purpose of the join
>> will require fetching additional columns.
>>
>> regards, tom lane
>>
>>
> Björn,
>
> I think, the timing difference you see between 2 queries is caused by delivering to the front-end (PgAdmin) and displaying all additional columns that you include in the second query (much bigger amount of data to pass from the db to the client).
> Pretty sure, if you do explain analyze on both queries, you'll see the same timing, because it'll reflect only db time without what's spent on delivering data to the client.
>
> Regards,
> Igor Neyman
>
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Bjoern_Wittich@gmx.de
Subject: Re: extremly bad select performance on huge table
In-Reply-To: <54473B2E.2080007@gmx.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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