Thread: performance on selecting a row in large tables
Hi guys, I am not really new to Postgres, but to be honest I am not an expert on the internal configuration of Postgres. I run a couple of PostgreSQL/PostGIS databases on several servers, but two of them are not performing very well. Those two databases are just read-only databases and they get dropped and recreated every week. I have a table like this (incl. a PostGIS geometry column): col01_id integer col02 character varying(10) col03 character varying(100) col04 double precision col05 double precision col06 character varying(80) col07 character varying(80) col07 character varying(40) col08 character varying(6) col09 character varying(100) col10 date col11 date col12 character varying(30) col13 character varying(30) col14 character varying(40) col15 character varying(10) col16 character varying(100) the_geom geometry About 2400000 rows are in the table, and the size of the table is about 2200MB. Three indexes are created on col01_id, col02 and on the geometry (clustered). When performing a 'select col01_id from table limit 1 offset 100000;', the query takes up to 20sec. Monitoring the dstats on the server, I see that the box is reading approx. 1GB from the disks. I ran VACUUM FULL on the table, reindex, analyze and cluster, all sorts of desperate tries to get a better performance out of this table without any success. I copied this table with less columns into a second table and run the same query on that table which performs much better. The server is a 2-dual-core cpu server with 2GB ram, running Fedora 5, Postgres 8.1.9 and PostGIS on top of it. Shared memory settings have been increased to: kernel.shmmax=1073741824 kernel.shmall=2097152 and I changed following settings in the postgres.conf: max_connections = 1000 shared_buffers = 65536 max_fsm_pages = 104000 My co-workes are getting to the point to move back to mySQL. But I like to stick to PostgreSQL. Looking forward for any comments or suggestions. Cheers, Rainer
"Rainer Spittel" <rainer.spittel@terralink.co.nz> writes:
> When performing a 'select col01_id from table limit 1 offset 100000;',
> the query takes up to 20sec. Monitoring the dstats on the server, I see
> that the box is reading approx. 1GB from the disks.
No surprise. That query says "read 100001 rows, then throw away the
first 100000".
Why are you considering that this is an important performance measure?
Are you aware that the results of such a query aren't even very well
defined (since you omitted an ORDER BY)?
regards, tom lane
Hi Rainer, On Tue, Feb 05, 2008 at 09:07:26AM +1300, Rainer Spittel wrote: > When performing a 'select col01_id from table limit 1 offset 100000;', As Tom already stated, the result of this query is a bit random since there is no ordering at all. What are you actually querying for? Try tuning queries before tuning the server. > Looking forward for any comments or suggestions. Here it is, right at the bottom of your mail: > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend explain analyze select col01_id from table limit 1 offset 100000; will run the query and show you how the query plan looks and how much time is spent. It is likely to say "SeqScan" somewhere - a sequential scan of the tabe. HTH! Tino. -- www.craniosacralzentrum.de www.spiritualdesign-chemnitz.de Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz
Hi Tom,
You are right, this query is not the right approach for performance
testing. I thought that this will give an indication about the
performance of a select statement on that table.
One of those slow queries are running on col02 which has a btree
index. But I use the 'in' expression to get a set of matching rows:
select * from table where col02 in ('...',[...],'...')
This query gets sometimes really slow, I guess it depends on the size
of the set used by 'in'.
Would the query perform better when I cluster the index on col02 and
force to order the set for the in clause?
Is there a way to disable the caching for testing? Once I ran the
query, the result set seems to be cached and the second run of the query
is fast. This makes a testing a little difficult ;-)
regards.
Rainer
"Rainer Spittel" <rainer.spittel@terralink.co.nz> writes:
> One of those slow queries are running on col02 which has a btree
> index. But I use the 'in' expression to get a set of matching rows:
> select * from table where col02 in ('...',[...],'...')
> This query gets sometimes really slow, I guess it depends on the size
> of the set used by 'in'.
Don't guess. Use EXPLAIN ANALYZE and find out what is happening.
regards, tom lane
Hi Rainer,
On Tue, Feb 05, 2008 at 10:24:11AM +1300, Rainer Spittel wrote:
> You are right, this query is not the right approach for performance
> testing. I thought that this will give an indication about the
> performance of a select statement on that table.
Only do benchmarking with real-world queries. Or you'll end up
optimizing for artificial workloads which never occur.
> One of those slow queries are running on col02 which has a btree
> index. But I use the 'in' expression to get a set of matching rows:
>
> select * from table where col02 in ('...',[...],'...')
>
> This query gets sometimes really slow, I guess it depends on the size
> of the set used by 'in'.
How much in-clauses are there? It might be faster to use a subselect if
the in clauses come from DB anyway.
> Would the query perform better when I cluster the index on col02 and
> force to order the set for the in clause?
> Is there a way to disable the caching for testing? Once I ran the
> query, the result set seems to be cached and the second run of the query
> is fast. This makes a testing a little difficult ;-)
Use "EXPLAIN query" first to get an estimate how the query will perform.
Get used to reading EXPLAIN output. You already seem to have tuned the
DB to your server (setting memory sizes etc.), now optimize queries
first, then the DB server (look at the settings like random_page_cost,
effective_cache_size etc. if the planner performs sequential scans and
you think it should use indices).
If you really want to rule out caching, you'd need to keep trashing your
OS's cache, e.g. by running a "while dd if=/dev/some-drive of=/dev/null
bs=1m ; do : ; done" in the background. But again, you'll optimize for a
artifical workload - in real live, the cache will play a significant
role. There's no use in optimizing queries for no-cache scenario if in
practice, the cache will have the data. I know of real-live applications
where the first query takes some time and all subsequent are fast.
That's what caches are for.
HTH,
Tino.
--
www.craniosacralzentrum.de
www.spiritualdesign-chemnitz.de
Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz