pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSmall Queries Really Fast, Large Queries Really Slow...
2+ messages / 2 participants
[nested] [flat]
* Small Queries Really Fast, Large Queries Really Slow...
@ 2010-06-24 10:45 Tom Wilcox <hungrytom@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Tom Wilcox @ 2010-06-24 10:45 UTC (permalink / raw)
To: pgsql-performance
Hi again!
I have finally got my Ubuntu VirtualBox VM running PostgreSQL with PL/Python
and am now looking at performance.
So here's the scenario:
We have a great big table:
cse=# \d nlpg.match_data
Table "nlpg.match_data"
Column | Type |
Modifiers
-------------------+----------+--------------------------------------------------------------------------
premise_id | integer |
usrn | bigint |
org | text |
sao | text |
level | text |
pao | text |
name | text |
street | text |
town | text |
postcode | text |
match_data_id | integer | not null default
nextval('nlpg.match_data_match_data_id_seq1'::regclass)
addr_str | text |
tssearch_name | tsvector |
tssearch_street | tsvector |
tssearch_addr_str | tsvector |
Indexes:
"match_data_pkey1" PRIMARY KEY, btree (match_data_id)
"index_match_data_mid" btree (match_data_id)
"index_match_data_pid" btree (premise_id)
"index_match_data_tssearch_addr_str" gin (tssearch_addr_str)
"index_match_data_tssearch_name" gin (tssearch_name)
"index_match_data_tssearch_street" gin (tssearch_street)
"index_match_data_usrn" btree (usrn)
KEY NOTE:
nlpg.match_data has approximately 27,000,000 rows..
Running this query:
EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org WHERE match_data_id <
1000000;
I get this:
"Index Scan using match_data_pkey1 on match_data (cost=0.00..1452207.14
rows=1913756 width=302) (actual time=23.448..61559.652 rows=999999 loops=1)"
" Index Cond: (match_data_id < 1000000)"
"Total runtime: 403855.675 ms"
I copied a chunk of the table like this:
CREATE TABLE nlpg.md_copy AS SELECT * FROM nlpg.match_data WHERE
match_data_id < 1000000;
Then ran the same query on the smaller copy table:
EXPLAIN ANALYZE UPDATE nlpg.md_copy SET org = org WHERE match_data_id <
1000000;
And get this:
"Seq Scan on md_copy (cost=0.00..96935.99 rows=999899 width=301) (actual
time=26.745..33944.923 rows=999999 loops=1)"
" Filter: (match_data_id < 1000000)"
"Total runtime: 57169.419 ms"
As you can see this is much faster per row with the smaller table chunk. I
then tried running the same first query with 10 times the number of rows:
EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org WHERE match_data_id <
10000000;
This takes a massive amount of time (still running) and is definitely a
non-linear increase in the run time in comparison with the previous query.
EXPLAIN UPDATE nlpg.match_data SET org = org WHERE match_data_id < 10000000;
"Seq Scan on match_data (cost=0.00..3980053.11 rows=19172782 width=302)"
" Filter: (match_data_id < 10000000)"
Any suggestions on what I can do to speed things up? I presume if I turn off
Sequential Scan then it might default to Index Scan.. Is there anything
else?
Cheers,
Tom
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Small Queries Really Fast, Large Queries Really Slow...
@ 2010-06-24 11:20 tv@fuzzy.cz
parent: Tom Wilcox <hungrytom@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: tv@fuzzy.cz @ 2010-06-24 11:20 UTC (permalink / raw)
To: Tom Wilcox <hungrytom@gmail.com>; +Cc: pgsql-performance
> Any suggestions on what I can do to speed things up? I presume if I turn
> off
> Sequential Scan then it might default to Index Scan.. Is there anything
> else?
>
> Cheers,
> Tom
Well, I doubt turning off the sequential scan will improve the performance
in this case - actually the first case (running 400 sec) uses an index
scan, while the 'fast' one uses sequential scan.
Actually I'd try exactly the oposite - disabling the index scan, i.e.
forcing it to use sequential scan in the first case. You're selecting
about 4% of the rows, but we don't know how 'spread' are those rows
through the table. It might happen PostgreSQL actually has to read all the
blocks of the table.
This might be improved by clustering - create and index on the
'match_data_id' colunm and then run
CLUSTER match_data_id_idx ON match_data;
This will sort the table accoring to match_data_id column, which should
improve the performance. But it won't last forever - it degrades through
time, so you'll have to perform clustering once a while (and it locks the
table, so be careful).
How large is the table anyway? How many rows / pages are there? Try
something like this
SELECT reltuples, relpages FROM pg_class WHERE relname = 'match_data';
Multiply the blocks by 8k and you'll get the occupied space. How much is
it? How much memory (shared_buffers) is there?
You could try partitioning accoring to the match_data_id column, but there
are various disadvantages related to foreign keys etc. and it's often a
major change in the application, so I'd consider other solutions first.
BTW I have no experience with running PostgreSQL inside a Virtual Box VM,
so it might be another source of problems. I do remember we had some
serious problems with I/O (network and disks) when running vmware, but it
was a long time ago and now it works fine. But maybe this the root cause?
Can you run dstat / vmstat / iostat or something like that in the host OS
to see which of the resources is causing problems (if any)?
Tomas
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-06-24 11:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-24 10:45 Small Queries Really Fast, Large Queries Really Slow... Tom Wilcox <hungrytom@gmail.com>
2010-06-24 11:20 ` tv@fuzzy.cz
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