pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance Question
19+ messages / 12 participants
[nested] [flat]
* Performance Question
@ 1999-02-04 02:25 Tim Perdue <tim@directricity.com>
1999-02-04 21:57 ` Re: [SQL] Performance Question Viorel Anghel <vang@altavista.net>
0 siblings, 1 reply; 19+ messages in thread
From: Tim Perdue @ 1999-02-04 02:25 UTC (permalink / raw)
To: pgsql-sql@hub.org
Please email directly to tim@dmcity.net.
Some of you may have followed the thread about my mailing archive system
that uses PostgreSQL.
The database is growing quickly and I want to make sure the performance is
going to be OK, so here's a question:
I have a table with records like this:
fld_mail_list (int) | etc | etc | fld_mail_body
1 x x x <----big TEXT Field ----->
2 x x x <----big TEXT Field ----->
1 x x x <----big TEXT Field ----->
4 x x x <----big TEXT Field ----->
1 x x x <----big TEXT Field ----->
1 x x x <----big TEXT Field ----->
7 x x x <----big TEXT Field ----->
1 x x x <----big TEXT Field ----->
1 x x x <----big TEXT Field ----->
7 x x x <----big TEXT Field ----->
1 x x x <----big TEXT Field ----->
8 x x x <----big TEXT Field ----->
Essentially, I have a several different mailings lists being archived into 1
big table, and the key to accessing them is using the fld_mail_list key.
Am I going to run into huge performance problems on this? Should each
mailing list be archived in its own table??
When I do a
SELECT * FROM tbl_mail_archive WHERE fld_mail_list=1 AND
fld_mail_body~~'%keyword%';
am I going to get killed with a performance hit?? It's running OK now, with
the table at 20MB, but I have 100MB more worth of letters to drop into the
table..... 8-)
Please email directly to tim@dmcity.net.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance Question
1999-02-04 02:25 Performance Question Tim Perdue <tim@directricity.com>
@ 1999-02-04 21:57 ` Viorel Anghel <vang@altavista.net>
0 siblings, 0 replies; 19+ messages in thread
From: Viorel Anghel @ 1999-02-04 21:57 UTC (permalink / raw)
To: pgsql-sql@hub.org; +Cc: tim@dmcity.net
On Wed, Feb 03, 1999 at 08:25:25PM -0600, Tim Perdue wrote:
> Please email directly to tim@dmcity.net.
hmm, and CC to list, we also want to know ;-)
[snip]
> am I going to get killed with a performance hit?? It's running OK now, with
> the table at 20MB, but I have 100MB more worth of letters to drop into the
> table..... 8-)
1. What does mean "OK now"? Just for my curiosity, on which machine and
which is the average time for such a query?
2. Why don't you test with the rest of 100MB and tell us what is happening?
--
___
<o-o> Viorel ANGHEL <vang@altavista.net>
[`-']
-"-"-
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [SQL] Performance Question
@ 1999-02-04 15:24 Tim Perdue <tim_perdue@yahoo.com>
0 siblings, 0 replies; 19+ messages in thread
From: Tim Perdue @ 1999-02-04 15:24 UTC (permalink / raw)
To: vang@altavista.net; pgsql-sql@hub.org
Queries take a couple seconds on a PowerPC 604 @ 225Mhz, running
LinuxPPC R4 with 112MB RAM.
I have indexed the fld_mail_body but it doesn't seem to get used??
--Tim
---Viorel Anghel <vang@altavista.net> wrote:
>
> On Wed, Feb 03, 1999 at 08:25:25PM -0600, Tim Perdue wrote:
> > Please email directly to tim@dmcity.net.
> hmm, and CC to list, we also want to know ;-)
>
> [snip]
>
> > am I going to get killed with a performance hit?? It's running OK
now, with
> > the table at 20MB, but I have 100MB more worth of letters to drop
into the
> > table..... 8-)
>
> 1. What does mean "OK now"? Just for my curiosity, on which machine
and
> which is the average time for such a query?
>
> 2. Why don't you test with the rest of 100MB and tell us what is
happening?
>
> --
> ___
> <o-o> Viorel ANGHEL <vang@altavista.net>
> [`-']
> -"-"-
>
>
==
______________________________________________________
Tim Perdue
tim@perdue.net
_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Performance Question
@ 1999-02-04 15:27 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Tom Lane @ 1999-02-04 15:27 UTC (permalink / raw)
To: tim@dmcity.net; +Cc: pgsql-sql@hub.org
"Tim Perdue" <tim@directricity.com> writes:
> Am I going to run into huge performance problems on this? Should each
> mailing list be archived in its own table??
> When I do a
> SELECT * FROM tbl_mail_archive WHERE fld_mail_list=1 AND
> fld_mail_body~~'%keyword%';
> am I going to get killed with a performance hit??
Should be OK as long as you make an index on fld_mail_list (and don't
forget to vacuum regularly).
Of course, maintaining that index is not zero-cost. The appropriate
thing to ask is what your usage patterns will be. If you frequently
make searches across multiple mailing lists, then you undoubtedly
want to do it as you show above. If you never (or hardly ever) do that,
you might as well keep each mailing list in its own table and live with
having to do multiple SELECTs when you do want to look across multiple
lists.
> It's running OK now, with the table at 20MB, but I have 100MB more
> worth of letters to drop into the table..... 8-)
I suspect your real problem is going to be that searching 100MB with
"fld_mail_body~~'%keyword%'" is going to be dog-slow. I think you
are going to want a full-text index if you expect to do that a lot.
There is a simple all-Postgres FTI in the contrib part of the
distribution, but I think it'd probably run out of steam long before you
got to 100MB. What I'd probably do in your situation is to use
Glimpse (http://glimpse.cs.arizona.edu/) for the text index.
That'd likely mean storing the message bodies in separate files outside
the database proper, and keeping only the file names in the database
rows. (But that'd get rid of the message-over-8K problem, so it isn't
all bad...)
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Performance Question
@ 1999-02-05 22:33 Tim Perdue, The Des Moines City.net <tim@dmcity.net>
0 siblings, 0 replies; 19+ messages in thread
From: Tim Perdue, The Des Moines City.net @ 1999-02-05 22:33 UTC (permalink / raw)
To: pgsql-sql@hub.org
I did finally get most of the data dropped into the Postgres database. It's
now sitting at 55,000 email messages (100MB).
Even with a huge mix of records, search time is not impacted as far as I can
tell. Queries come back just as quickly as they did before.
I did discover that indexes on the text fields are not being used when I use
a Regex operator. So I dropped the index with no performance hit.
Thanks to everyone for their input,
Tim Perdue
geocrawler.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* performance question
@ 2003-01-20 11:40 Moritz Lennert <moritz.lennert@ulb.ac.be>
0 siblings, 0 replies; 19+ messages in thread
From: Moritz Lennert @ 2003-01-20 11:40 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512
MB RAM.
Some queries I launch take quite a long time, and I'm wondering whether
this is normal,or whether I can get better performance somehow.
As an example, I have a field which is in char(2), with 23 different
possible values. When I launch a "select * from table where field = 'xx'"
this takes a very long time (some 230194.10 msec, i.e. almost 4 minutes,
according to "explain analyze"). I've tried indexing on this column and
vacuuming ("vacuum analyze"), but this doesn't change anything. "explain
select" always gives me a sequential scan. The correlation value in
pg_stats for this column is 0.0583268, so a seq scan is probably cheaper
than index scan.
Now maybe this is just the way it is and there is no possibility to
enhance performance, but if someone has some tips on what I might try,
they are very welcome !
One question I asked myself is whether the use of char(2) is the best
option. The column (and most others in the table) contains codes that
designate different characteristics (for ex. in a column 'sex' one would
find '1'=male, '2'=female).
Moritz
^ permalink raw reply [nested|flat] 19+ messages in thread
* performance question
@ 2003-01-20 11:40 Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 15:13 ` Re: performance question Manfred Koizar <mkoi-pg@aon.at>
2003-01-20 18:08 ` Re: performance question Josh Berkus <josh@agliodbs.com>
2003-01-20 20:38 ` Re: performance question Tom Lane <tgl@sss.pgh.pa.us>
2003-01-20 21:05 ` Re: performance question Tomasz Myrta <jasiek@klaster.net>
0 siblings, 4 replies; 19+ messages in thread
From: Moritz Lennert @ 2003-01-20 11:40 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512
MB RAM.
Some queries I launch take quite a long time, and I'm wondering whether
this is normal,or whether I can get better performance somehow.
As an example, I have a field which is in char(2), with 23 different
possible values. When I launch a "select * from table where field = 'xx'"
this takes a very long time (some 230194.10 msec, i.e. almost 4 minutes,
according to "explain analyze"). I've tried indexing on this column and
vacuuming ("vacuum analyze"), but this doesn't change anything. "explain
select" always gives me a sequential scan. The correlation value in
pg_stats for this column is 0.0583268, so a seq scan is probably cheaper
than index scan.
Now maybe this is just the way it is and there is no possibility to
enhance performance, but if someone has some tips on what I might try,
they are very welcome !
One question I asked myself is whether the use of char(2) is the best
option. The column (and most others in the table) contains codes that
designate different characteristics (for ex. in a column 'sex' one would
find '1'=male, '2'=female).
Moritz
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
@ 2003-01-20 15:13 ` Manfred Koizar <mkoi-pg@aon.at>
2003-01-20 17:10 ` Re: performance question Moritz Lennert <mlennert@club.worldonline.be>
3 siblings, 1 reply; 19+ messages in thread
From: Manfred Koizar @ 2003-01-20 15:13 UTC (permalink / raw)
To: mlennert@club.worldonline.be; +Cc: pgsql-sql
On Mon, 20 Jan 2003 12:40:34 +0100 (CET), "Moritz Lennert"
<mlennert@club.worldonline.be> wrote:
>I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512
>MB RAM.
>Some queries I launch take quite a long time, and I'm wondering whether
>this is normal,or whether I can get better performance somehow.
Moritz, we need more information. Please show us
. your PG version
. CREATE TABLE ...
. indices
. your query
. EXPLAIN ANALYZE output
. your settings, especially shared_buffers, sort_mem,
random_page_cost, effective_cache_size
>One question I asked myself is whether the use of char(2) is the best
>option. The column (and most others in the table) contains codes that
>designate different characteristics (for ex. in a column 'sex' one would
>find '1'=male, '2'=female).
char(2) needs 8 bytes, smallint only 2 bytes (unless followed by a
column with 4 or 8 byte alignment). Instead of char(1) (8 bytes) you
might want to use the Postgres specific type "char" (with the double
quotes!) needing only 1 byte.
Servus
Manfred
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 15:13 ` Re: performance question Manfred Koizar <mkoi-pg@aon.at>
@ 2003-01-20 17:10 ` Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 18:27 ` Re: performance question Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 1 reply; 19+ messages in thread
From: Moritz Lennert @ 2003-01-20 17:10 UTC (permalink / raw)
To: Manfred Koizar <mkoi-pg@aon.at>; +Cc: pgsql-sql
> On Mon, 20 Jan 2003 16:42:12 +0100 (CET), "Moritz Lennert"
> <mlennert@club.worldonline.be> wrote:
>>explain analyze select commune_residence, type_diplome from rec81 where
>>type_diplome = '11';
>>NOTICE: QUERY PLAN:
>>
>>Seq Scan on rec81 (cost=0.00..120316.30 rows=177698 width=15) (actual
>>time=23.03..219164.82 rows=176621 loops=1)
>>Total runtime: 226149.03 msec
>
> Assumung that there are no NULLs, I guesstimate that 25 tuples should
> fit onto one page (could be more than 40 if you use smallint and
> "char"). That gives 88K pages for 2.2M tuples. However,
> cost=0.00..120316.30 tells us that there are almost 120K pages. Time
> to do a VACUUM FULL?
>
I'll try that, although I haven't changed any of the tuples since import
of the data (this is a static table...)
> From what I've seen I think that the planner is right to choose a seq
> scan. 226 seconds for reading 120K pages (~ 1GB) is not very
> impressive, though. What kind of disk do you have?
IDE, Samsung, 7200rpm
> Is your disk heavily fragmented?
It shouldn't be.
> Did you enable DMA?
No, should I ?
> What else was running on
> your machine while you did that VACUUM ANALYZE?
Mozilla, maybe xterm with vi...
>>shared_buffers = 128
>
> It won't help much for this query we are discussing, but I would
> recommend setting shared_buffers to something in the range [1000,
> 4000].
>
> And one of my favorites: effective_cache_size = 40000
>
I will have to increase /proc/sys/kernel/shmmax for that, or ?
Thanks again !
Moritz
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 15:13 ` Re: performance question Manfred Koizar <mkoi-pg@aon.at>
2003-01-20 17:10 ` Re: performance question Moritz Lennert <mlennert@club.worldonline.be>
@ 2003-01-20 18:27 ` Manfred Koizar <mkoi-pg@aon.at>
0 siblings, 0 replies; 19+ messages in thread
From: Manfred Koizar @ 2003-01-20 18:27 UTC (permalink / raw)
To: mlennert@club.worldonline.be; +Cc: pgsql-sql
On Mon, 20 Jan 2003 18:10:24 +0100 (CET), "Moritz Lennert"
<mlennert@club.worldonline.be> wrote:
>I'll try that, although I haven't changed any of the tuples since import
>of the data (this is a static table...)
Then I must have miscalculated something :-( What does VACUUM VERBOSE
ANALYZE <yourtable> say?
>> From what I've seen I think that the planner is right to choose a seq
>> scan. 226 seconds for reading 120K pages (~ 1GB) is not very
>> impressive, though. What kind of disk do you have?
>
>IDE, Samsung, 7200rpm
>
>> Is your disk heavily fragmented?
>
>It shouldn't be.
>
>> Did you enable DMA?
>
>No, should I ?
Yes. Here is what I got on a P IV 2 GHz with a Seagate 7200rpm(?)
disk:
~ # hdparm -t -T /dev/hda
/dev/hda:
Timing buffer-cache reads: 128 MB in 0.39 seconds =328.21 MB/sec
Timing buffered disk reads: 64 MB in 2.49 seconds = 25.70 MB/sec
vs.
~ # hdparm -t -T /dev/hda
/dev/hda:
Timing buffer-cache reads: 128 MB in 0.37 seconds =345.95 MB/sec
Timing buffered disk reads: 64 MB in 23.38 seconds = 2.74 MB/sec
~ # l xx
-rw-r--r-- 1 root root 1332104434 2003-01-20 19:04 xx
~ # time dd if=xx of=/dev/null bs=8k
162610+1 Records in
162610+1 Records out
real 0m48.665s
user 0m0.150s
sys 0m1.690s
~ # hdparm -d 0 /dev/hda
~ # time dd if=xx of=/dev/null bs=8k
162610+1 Records in
162610+1 Records out
real 7m42.666s
user 0m0.270s
sys 1m27.160s
With DMA: More than 3000 pages / second
Without DMA: ~ 350 pages / second
Your throughput: ~ 530 pages / second
>> recommend setting shared_buffers to something in the range [1000,
>> 4000].
>> And one of my favorites: effective_cache_size = 40000
>
>I will have to increase /proc/sys/kernel/shmmax for that, or ?
Maybe for shared_buffers. Certainly not for effective_cache_size.
The latter does not consume resources, it's just a hint for the
planner.
Servus
Manfred
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
@ 2003-01-20 18:08 ` Josh Berkus <josh@agliodbs.com>
2003-01-20 19:34 ` Re: performance question Moritz Lennert <mlennert@club.worldonline.be>
3 siblings, 1 reply; 19+ messages in thread
From: Josh Berkus @ 2003-01-20 18:08 UTC (permalink / raw)
To: mlennert@club.worldonline.be; pgsql-sql
Moritz,
There is a performance mailing list at:
pgsql-performance@postgresql.org
--Josh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 18:08 ` Re: performance question Josh Berkus <josh@agliodbs.com>
@ 2003-01-20 19:34 ` Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 22:20 ` Re: performance question Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 19+ messages in thread
From: Moritz Lennert @ 2003-01-20 19:34 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
> Moritz,
>
> There is a performance mailing list at:
>
> pgsql-performance@postgresql.org
>
> --Josh
I'm sorry, I didn't realize this, it is certainly closer to what I need.
Next time I'll look better (google brought up references to this list, so
I subscribed here).
Thanks for the hint,
Moritz
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 18:08 ` Re: performance question Josh Berkus <josh@agliodbs.com>
2003-01-20 19:34 ` Re: performance question Moritz Lennert <mlennert@club.worldonline.be>
@ 2003-01-20 22:20 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 19+ messages in thread
From: Josh Berkus @ 2003-01-20 22:20 UTC (permalink / raw)
To: mlennert@club.worldonline.be; +Cc: pgsql-sql
Moritz,
> I'm sorry, I didn't realize this, it is certainly closer to what I
> need.
> Next time I'll look better (google brought up references to this
> list, so
> I subscribed here).
Hey, there are 18 active lists now ... we don't expect anyone to get
the right one right off!
-Josh Berkus
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
@ 2003-01-20 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-21 05:32 ` Re: performance question Vernon Wu <vernonw@gatewaytech.com>
3 siblings, 1 reply; 19+ messages in thread
From: Tom Lane @ 2003-01-20 20:38 UTC (permalink / raw)
To: mlennert@club.worldonline.be; +Cc: pgsql-sql
"Moritz Lennert" <mlennert@club.worldonline.be> writes:
> One question I asked myself is whether the use of char(2) is the best
> option.
It may not be, particularly if you are running in a non-English locale.
Comparisons will invoke the standard library routine strcoll(), which is
depressingly slow in some locales, at least on some platforms.
> The column (and most others in the table) contains codes that
> designate different characteristics (for ex. in a column 'sex' one would
> find '1'=male, '2'=female).
If you are using numeric codes you are surely better off storing them
as integer or perhaps smallint (integer is less likely to create type
mismatch headaches, though). In the above example you are getting the
worst of both worlds: there's no mnemonic value in your data entries,
*and* you're paying for textual comparisons.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 20:38 ` Re: performance question Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-21 05:32 ` Vernon Wu <vernonw@gatewaytech.com>
0 siblings, 0 replies; 19+ messages in thread
From: Vernon Wu @ 2003-01-21 05:32 UTC (permalink / raw)
To: mlennert@club.worldonline.be, Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
20/01/2003 12:38:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>"Moritz Lennert" <mlennert@club.worldonline.be> writes:
>> One question I asked myself is whether the use of char(2) is the best
>> option.
>
>It may not be, particularly if you are running in a non-English locale.
>Comparisons will invoke the standard library routine strcoll(), which is
>depressingly slow in some locales, at least on some platforms.
>
In the case of selection operations involving multiple tables joined by userid that can be in various languages, is a
potential performance trouble spot? Considering the number of tables anywhere from ten to twenty.
>> The column (and most others in the table) contains codes that
>> designate different characteristics (for ex. in a column 'sex' one would
>> find '1'=male, '2'=female).
>
>If you are using numeric codes you are surely better off storing them
>as integer or perhaps smallint (integer is less likely to create type
>mismatch headaches, though). In the above example you are getting the
>worst of both worlds: there's no mnemonic value in your data entries,
>*and* you're paying for textual comparisons.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
@ 2003-01-20 21:05 ` Tomasz Myrta <jasiek@klaster.net>
3 siblings, 0 replies; 19+ messages in thread
From: Tomasz Myrta @ 2003-01-20 21:05 UTC (permalink / raw)
To: mlennert@club.worldonline.be; +Cc: pgsql-sql
Moritz Lennert wrote:
>Hello,
>
>I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512
>MB RAM.
>Some queries I launch take quite a long time, and I'm wondering whether
>this is normal,or whether I can get better performance somehow.
>
>As an example, I have a field which is in char(2), with 23 different
>possible values.
This field is not too selective. Are there any more fields which are always in where condition?
If yes, indexing on more than one field should help.
Tomasz Myrta
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
@ 2003-01-20 16:45 Moritz Lennert <mlennert@club.worldonline.be>
0 siblings, 0 replies; 19+ messages in thread
From: Moritz Lennert @ 2003-01-20 16:45 UTC (permalink / raw)
To: pgsql-sql
> On Mon, 20 Jan 2003 12:40:34 +0100 (CET), "Moritz Lennert"
> <mlennert@club.worldonline.be> wrote:
>>I have a table with some 2.2 million rows on a Pentium4, 1.8GHz with 512
MB RAM.
>>Some queries I launch take quite a long time, and I'm wondering whether
this is normal,or whether I can get better performance somehow.
>
> Moritz, we need more information. Please show us
> . your PG version
7.2.1-2woody2
> . CREATE TABLE ...
CREATE TABLE "rec81" (
"commune_residence" character(5),
"sexe" character(1),
"annee_naissance" smallint,
"mois_naissance" smallint,
"jour_naissance" smallint,
"parent" character(2),
"etat_civil" character(1),
"nationalite" character(3),
"type_menage" character(1),
"logement_depuis_naiss" character(1),
"domicile_mere" character(6),
"dans_log_depuis_quand" smallint,
"meme_log_1980" character(1),
"commune_1980" character(6),
"annee_entree_belgique" smallint,
"age_fin_etude" smallint,
"detenteur_diplome" character(1),
"type_diplome" character(2),
"detenteur_diplome_etranger" character(1),
"actif" character(1),
"actif_temporaire" character(1),
"type_profession" character(4),
"statut_professionnel" character(1),
"temps_partiel" character(1),
"nombre_heures_travail" smallint,
"employeur" character(1),
"nombre_personnes_ds_services" integer,
"direction" character(1),
"lieu_travail" character(6),
"secteur_activite" character(3),
"emploi_complementaire" character(1),
"type_emploi_complementaire" character(4),
"lieu_depart_navette" character(1),
"commune_depart_navette" character(6),
"distance" smallint,
"nbre_navettes_par_jour" character(1),
"nbre_jours_navette_par_semaine" character(1),
"type_transport_navette" character(3),
"duree_trajet_navette" character(1),
"statut_non_occupe" character(2),
"effectif_menage" smallint,
"sec_stat_residence" character(6)
);
> . indices
CREATE INDEX rec81_commune_residence_idx ON rec81 USING btree
(commune_residence);
CREATE INDEX rec81_annee_naissance_idx ON rec81 USING btree
(annee_naissance);
CREATE INDEX rec81_nationalite_idx ON rec81 USING btree (nationalite);
CREATE INDEX rec81_meme_log_1980_idx ON rec81 USING btree (meme_log_1980);
CREATE INDEX rec81_commune_1980_idx ON rec81 USING btree (commune_1980);
CREATE INDEX rec81_age_fin_etude_idx ON rec81 USING btree (age_fin_etude);
CREATE INDEX rec81_detenteur_diplome_idx ON rec81 USING btree
(detenteur_diplome);
CREATE INDEX rec81_type_profession_idx ON rec81 USING btree
(type_profession);
CREATE INDEX rec81_statut_professionnel_idx ON rec81 USING btree
(statut_professionnel);
CREATE INDEX rec81_lieu_travail_idx ON rec81 USING btree (lieu_travail);
CREATE INDEX rec81_secteur_activite_idx ON rec81 USING btree
(secteur_activite);
CREATE INDEX rec81_statut_non_occupe_idx ON rec81 USING btree
(statut_non_occupe);
CREATE INDEX rec81_sec_stat_residence_idx ON rec81 USING btree
(sec_stat_residence);
CREATE INDEX rec81_comres_typedipl_idx ON rec81 USING btree
commune_residence, type_diplome);
CREATE INDEX rec81_type_diplome_idx ON rec81 USING btree (type_diplome);
> . your query
select commune_residence, type_diplome from rec81 where type_diplome = '11';
> . EXPLAIN ANALYZE output
explain analyze select commune_residence, type_diplome from rec81 where
type_diplome = '11';
NOTICE: QUERY PLAN:
Seq Scan on rec81 (cost=0.00..120316.30 rows=177698 width=15) (actual
time=23.03..219164.82 rows=176621 loops=1)
Total runtime: 226149.03 msec
EXPLAIN
> . your settings, especially shared_buffers, sort_mem,
> random_page_cost, effective_cache_size
shared_buffers = 128
The others are not set (I suppose they should be set in the
postgresql.conf file ?)
Thanks for your help !
Moritz
^ permalink raw reply [nested|flat] 19+ messages in thread
* performance question
@ 2009-05-08 15:36 Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-08 17:55 ` Re: performance question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 19+ messages in thread
From: Oliveiros Cristina @ 2009-05-08 15:36 UTC (permalink / raw)
To: pgsql-sql
Hi, All
Suppose you have a query like this
SELECT *
FROM t_sitesresumebydate a
JOIN t_triple_association c
ON "IDSiteResume" = "IDResume"
WHERE "dtDate" BETWEEN '2009-2-1'
AND '2009-2-3'
AND "IDHitsAccount" = 378284
With this time interval it completes in less than a second.
If I add one day to the condition, i.e., if it becomes BETWEEN '2009-2-1' AND '2009-2-4'
the performance is radically different, it jumps to a staggering 424 seconds. and the number of records returned is just 117 (against 79, by the former condition)
Frankly, I cannot understand the reason for this, it seems the planner is taking radically diferent plans on the two conditions (they are below).
I have an index on all the fields used in the query.
Can anyone help me in fixing this, please?
Thanks in advance for your kind help
Best,
Oliveiros
"Hash Join (cost=46644.50..751271.16 rows=117 width=60) (actual time=15821.110..424120.924 rows=247 loops=1)"
" Hash Cond: (c."IDResume" = a."IDSiteResume")"
" -> Seq Scan on t_triple_association c (cost=0.00..554934.99 rows=29938099 width=32) (actual time=38.253..392251.754 rows=30101626 loops=1)"
" -> Hash (cost=46644.30..46644.30 rows=82 width=28) (actual time=2711.356..2711.356 rows=23 loops=1)"
" -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=82 width=28) (actual time=881.146..2711.303 rows=23 loops=1)"
" Index Cond: ("IDHitsAccount" = 378284)"
" Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-04'::date))"
"Total runtime: 424121.180 ms"
"Nested Loop (cost=108.43..745296.34 rows=79 width=60) (actual time=44.283..311.942 rows=185 loops=1)"
" -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=55 width=28) (actual time=5.825..23.828 rows=17 loops=1)"
" Index Cond: ("IDHitsAccount" = 378284)"
" Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-03'::date))"
" -> Bitmap Heap Scan on t_triple_association c (cost=108.43..12658.83 rows=3515 width=32) (actual time=16.902..16.910 rows=11 loops=17)"
" Recheck Cond: (a."IDSiteResume" = c."IDResume")"
" -> Bitmap Index Scan on "index" (cost=0.00..108.43 rows=3515 width=0) (actual time=14.466..14.466 rows=11 loops=17)"
" Index Cond: (a."IDSiteResume" = c."IDResume")"
"Total runtime: 312.192 ms"
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: performance question
2009-05-08 15:36 performance question Oliveiros Cristina <oliveiros.cristina@marktest.pt>
@ 2009-05-08 17:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Tom Lane @ 2009-05-08 17:55 UTC (permalink / raw)
To: Oliveiros Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
"Oliveiros Cristina" <oliveiros.cristina@marktest.pt> writes:
> Frankly, I cannot understand the reason for this, it seems the planner is taking radically diferent plans on the two conditions (they are below).
Yup, and you seem to be near the crossover point where it thinks they
have equal cost. You need to be fixing the inaccurate cost estimates.
The most obvious problem is the bad rowcount estimate here:
> " -> Bitmap Index Scan on "index" (cost=0.00..108.43 rows=3515 width=0) (actual time=14.466..14.466 rows=11 loops=17)"
> " Index Cond: (a."IDSiteResume" = c."IDResume")"
Perhaps increasing the statistics targets for one or both tables would
help on that.
Another odd thing is that essentially identical indexscans are taking
radically different times:
> " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=82 width=28) (actual time=881.146..2711.303 rows=23 loops=1)"
> " Index Cond: ("IDHitsAccount" = 378284)"
> " Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-04'::date))"
> " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=55 width=28) (actual time=5.825..23.828 rows=17 loops=1)"
> " Index Cond: ("IDHitsAccount" = 378284)"
> " Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-03'::date))"
I think probably the second one was fast because the data is already
cached, so you're not making an entirely fair comparison. If your
expectation is that the database is going to be operating under mostly
cached conditions, then you probably ought to adjust the planner cost
parameters to reflect that (look at effective_cache_size, and try
reducing random_page_cost).
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2009-05-08 17:55 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-04 02:25 Performance Question Tim Perdue <tim@directricity.com>
1999-02-04 21:57 ` Viorel Anghel <vang@altavista.net>
1999-02-04 15:24 Re: [SQL] Performance Question Tim Perdue <tim_perdue@yahoo.com>
1999-02-04 15:27 Re: Performance Question Tom Lane <tgl@sss.pgh.pa.us>
1999-02-05 22:33 Re: Performance Question Tim Perdue, The Des Moines City.net <tim@dmcity.net>
2003-01-20 11:40 performance question Moritz Lennert <moritz.lennert@ulb.ac.be>
2003-01-20 11:40 performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 15:13 ` Re: performance question Manfred Koizar <mkoi-pg@aon.at>
2003-01-20 17:10 ` Re: performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 18:27 ` Re: performance question Manfred Koizar <mkoi-pg@aon.at>
2003-01-20 18:08 ` Re: performance question Josh Berkus <josh@agliodbs.com>
2003-01-20 19:34 ` Re: performance question Moritz Lennert <mlennert@club.worldonline.be>
2003-01-20 22:20 ` Re: performance question Josh Berkus <josh@agliodbs.com>
2003-01-20 20:38 ` Re: performance question Tom Lane <tgl@sss.pgh.pa.us>
2003-01-21 05:32 ` Re: performance question Vernon Wu <vernonw@gatewaytech.com>
2003-01-20 21:05 ` Re: performance question Tomasz Myrta <jasiek@klaster.net>
2003-01-20 16:45 Re: performance question Moritz Lennert <mlennert@club.worldonline.be>
2009-05-08 15:36 performance question Oliveiros Cristina <oliveiros.cristina@marktest.pt>
2009-05-08 17:55 ` Re: performance question Tom Lane <tgl@sss.pgh.pa.us>
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