Thread: select slow?
hi all,
i have an amd athlon with 256 ram (i know, this is not a *real* server but
my tables are small)
i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc.
when i do a select in took long to execute, here is an example
table icc_m_banco
CREATE TABLE ICC_M_BANCO (
CodBanco SMALLINT NOT NULL,
Descripcion CHARACTER VARYING(60) NOT NULL,
RefContable NUMERIC,
Estado CHAR(1) NOT NULL,
FecRegistro DATE NOT NULL,
CONSTRAINT EstadoBanco CHECK ((Estado = 'A') or (Estado = 'I')),
PRIMARY KEY(CodBanco)
);
select * from icc_m_banco where codbanco = 1;
it tooks 13s from it's send until it's executed.
explain analyze give me this result:
explain analyze
select * from icc_m_banco where codbanco = 1;
Seq Scan on icc_m_banco (cost=0.00..1.06 rows=6 width=41) (actual
time=7.94..7.96 rows=4 loops=1)
Total runtime: 63.37 msec
(2 rows)
so i think its not a database problem (at least it's not all the problem),
though it seems to me it is taking a lot of time executing this.
am i right? any suggestions?
_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
On 30/03/2004 20:25 Jaime Casanova wrote: > hi all, > > > i have an amd athlon with 256 ram (i know, this is not a *real* server > but my tables are small) > > i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc. > > when i do a select in took long to execute, here is an example > > > table icc_m_banco > > CREATE TABLE ICC_M_BANCO ( > CodBanco SMALLINT NOT NULL, > Descripcion CHARACTER VARYING(60) NOT NULL, > RefContable NUMERIC, > Estado CHAR(1) NOT NULL, > FecRegistro DATE NOT NULL, > CONSTRAINT EstadoBanco CHECK ((Estado = 'A') or (Estado = 'I')), > PRIMARY KEY(CodBanco) > ); > > > select * from icc_m_banco where codbanco = 1; select * from icc_m_banco where codbanco = 1::int2; -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
On Tuesday 30 March 2004 20:25, Jaime Casanova wrote: > hi all, > > > i have an amd athlon with 256 ram (i know, this is not a *real* server but > my tables are small) Nothing wrong with it - it's what I still use as my development server. > i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc. > > when i do a select in took long to execute, here is an example > CREATE TABLE ICC_M_BANCO ( > CodBanco SMALLINT NOT NULL, > select * from icc_m_banco where codbanco = 1; > > it tooks 13s from it's send until it's executed. Try: SELECT * FROM icc_m_banco WHERE codbanco = 1::smallint; By default, PG will treat a numeric constant as integer not smallint, so when it looks for an index it can't find one for integer, so scans instead. -- Richard Huxton Archonet Ltd
> >On Tuesday 30 March 2004 20:25, Jaime Casanova wrote: >>hi all, > > > > > > i have an amd athlon with 256 ram (i know, this is not a *real* server >but > > my tables are small) >Nothing wrong with it - it's what I still use as my development server. > > > i'm using vb6 (win98) with pgsql-7.3.4 (rh8) trough the psqlodbc. > > > > when i do a select in took long to execute, here is an example > > > CREATE TABLE ICC_M_BANCO ( > > CodBanco SMALLINT NOT NULL, > > select * from icc_m_banco where codbanco = 1; > > > > it tooks 13s from it's send until it's executed. > >Try: > SELECT * FROM icc_m_banco WHERE codbanco = 1::smallint; > >By default, PG will treat a numeric constant as integer not smallint, so >when >it looks for an index it can't find one for integer, so scans instead. > >-- > Richard Huxton > Archonet Ltd There are no indexes yet, and the table is just 6 rows long so even if indexes exists the planner will do a seq scan. that's my whole point 63m for seq scan in 6 rows table is too much. _________________________________________________________________ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail
"Jaime Casanova" <el_vigia_ec@hotmail.com> writes:
> There are no indexes yet, and the table is just 6 rows long so even if
> indexes exists the planner will do a seq scan. that's my whole point 63m for
> seq scan in 6 rows table is too much.
That was 63 milliseconds, according to your original post, which seems
perfectly reasonable to me seeing that it's not a super-duper server.
The problem sounds to be either on the client side or somewhere in your
network. I don't know anything about VB, but you might want to look
through the client-side operations to see what could be eating up the 13
seconds.
regards, tom lane
On 31/03/2004 16:40 Tom Lane wrote: > "Jaime Casanova" <el_vigia_ec@hotmail.com> writes: > > There are no indexes yet, and the table is just 6 rows long so even if > > indexes exists the planner will do a seq scan. that's my whole point > 63m for > > seq scan in 6 rows table is too much. > > That was 63 milliseconds, according to your original post, which seems > perfectly reasonable to me seeing that it's not a super-duper server. > > The problem sounds to be either on the client side or somewhere in your > network. I don't know anything about VB, but you might want to look > through the client-side operations to see what could be eating up the 13 > seconds. Given that the client and server are on different machines, I'm wondering the bulk of the 13 seconds is due a network mis-configuration or a very slow DNS server... -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
As part of my ongoing evaluation of PostgreSQL I have been doing a little stress testing. I though I would share an interesting result here.. Machine spec: 500 MHz PIII 256MB RAM "old-ish" IDE HD (5400RPM) Linux 2.4.22 kernel (Madrake 9.2) I have PostgreSQL 7.4.1 installed and have managed to load up a 1.4 GB database from MS SQLServer. Vaccum analyzed it. As a test in PosgreSQL I issued a statement to update a single column of a table containing 2.8 million rows with the values of a column in a table with similar rowcount. Using the above spec I had to stop the server after 17 hours. The poor thing was thrashing the hard disk and doing more swapping than useful work. Having obtained a copy of Mandrake 10.0 with the 2.6 kernal I though I would give it a go. Same hardware. Same setup. Same database loaded up. Same postgresql.conf file to make sure all the settings were the same. Vaccum analyzed it. same update statement COMPLETED in 2 hours 50 minutes. I'm impressed. I could see from vmstat that the system was achieving much greater IO thoughput than the 2.4 kernel. Although the system was still swapping there seems to be a completely different memory management pattern that suits PostgreSQL very well. Just to see that this wasn't a coincidence I am repeating the test. It is now into the 14th hour using the old 2.4 kernel. I'm going to give up..... Has anyone else done any comparative testing with the 2.6 kernel? Cheers, Gary.
"Gary Doades" <gpd@gpdnet.co.uk> writes:
> As a test in PosgreSQL I issued a statement to update a single column
> of a table containing 2.8 million rows with the values of a column in
> a table with similar rowcount. Using the above spec I had to stop the
> server after 17 hours. The poor thing was thrashing the hard disk and
> doing more swapping than useful work.
This statement is pretty much content-free, since you did not show us
the table schemas, the query, or the EXPLAIN output for the query.
(I'll forgive you the lack of EXPLAIN ANALYZE, but you could easily
have provided all the other hard facts.) There's really no way to tell
where the bottleneck is. Maybe it's a kernel-level issue, but I would
not bet on that without more evidence. I'd definitely not bet on it
without direct confirmation that the same query plan was used in both
setups.
regards, tom lane
The post was not intended to be content-rich, just my initial feedback after only just switching to 2.6. Since I had largely given up on this particular line of attack using 2.4 I didn't think to do a detailed analysis at this time. I was also hoping that others would add to the discussion. As this could become important I will be doing more analysis, but due to the nature of the issue and trying to keep as many factors constant as possible, this may take some time. Cheers, Gary. On 2 Apr 2004 at 1:32, Tom Lane wrote: > "Gary Doades" <gpd@gpdnet.co.uk> writes: > > As a test in PosgreSQL I issued a statement to update a single column > > of a table containing 2.8 million rows with the values of a column in > > a table with similar rowcount. Using the above spec I had to stop the > > server after 17 hours. The poor thing was thrashing the hard disk and > > doing more swapping than useful work. > > This statement is pretty much content-free, since you did not show us > the table schemas, the query, or the EXPLAIN output for the query. > (I'll forgive you the lack of EXPLAIN ANALYZE, but you could easily > have provided all the other hard facts.) There's really no way to tell > where the bottleneck is. Maybe it's a kernel-level issue, but I would > not bet on that without more evidence. I'd definitely not bet on it > without direct confirmation that the same query plan was used in both > setups. > > regards, tom lane > > > -- > Incoming mail is certified Virus Free. > Checked by AVG Anti-Virus (http://www.grisoft.com). > Version: 7.0.230 / Virus Database: 262.6.5 - Release Date: 31/03/2004 >
Gary Doades wrote: > > Has anyone else done any comparative testing with the 2.6 kernel? > I know for a fact that certain stuff is recognized differently between 2.2, 2.4 and 2.6 kernels. For example i have one box that i installed debian stable on that used a 2.2 kernel which automatically tuned on DMA on the harddrive, didn't do it on a 2.4 kernel, but on 2.6 one it saw it as DMA able. Such things can dramatically affect performance, so make sure to compare what capabilities the kernel thinks your hardware has between the kernels first... But i'll grant that the 2.6 kernel is a great deal faster on some of our test servers. Regards Magnus