[PERFORM] Effective Cache Size
Hi, I have been following a thread on this list "Inconsistent performance" and had a few questions especially the bits about effective_cache_size. I have read some of the docs, and some other threads on this setting, and it seems to used by the planner to either choose a sequential or index scan. So it will not necessarily increase performance I suppose but instead choose the most optimal plan. Is this correct? We are not that we are suffering massive performance issues at the moment but it is expected that our database is going to grow considerably in the next couple of years, both in terms of load and size. Also what would an appropriate setting be? >From what I read of Scott Marlowes email, and from the information below I reckon it should be somewhere in the region of 240,000. Danger maths ahead. Beware 141816K buff + 1781764K cached - 1923580K total effective_cache_size = 1923580 / 8 = 240447.5 Here is some information on the server in question. If any more information is required then please say. It is a dedicated PG machine with no other services being hosted off it. As you can see from the uptime, its load average is 0.00, and is currently so chilled its almost frozen! That will change though :-( Hardware Dual PIII 1.4GHz 2Gb RAM 1Tb SAN with hardware RAID 5 using 1Gbps Fibre channel. OS == Linux webbasedth5 2.4.18-18.7.xsmp #1 SMP Wed Nov 13 19:01:42 EST 2002 i686 Red Hat Linux release 7.3 (Valhalla) PG == PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96 Database This includes all indexes and tables. I can provide more information on how this is chopped up if needed. Size : 1,141.305 Mb Tuples : 13,416,397 Uptime == 11:15am up 197 days, 16:50, 1 user, load average: 0.00, 0.00, 0.00 Top === Mem: 2064836K av, 2018648K used, 46188K free, 0K shrd, 141816K buff Swap: 2096472K av,4656K used, 2091816K free 1781764K cached Postgresql.conf (all defaults except) = max_connections = 1000 shared_buffers = 16000 (128 Mb) max_fsm_relations = 5000 max_fsm_pages = 50 vacuum_mem = 65535 Kind Regards, Nick Barr This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Effective Cache Size
On 17 Sep 2003 at 11:48, Nick Barr wrote: > Hi, > > I have been following a thread on this list "Inconsistent performance" > and had a few questions especially the bits about effective_cache_size. > I have read some of the docs, and some other threads on this setting, > and it seems to used by the planner to either choose a sequential or > index scan. So it will not necessarily increase performance I suppose > but instead choose the most optimal plan. Is this correct? That is correct. > Danger maths ahead. Beware > > > 141816K buff > + 1781764K cached > - > 1923580K total > > effective_cache_size = 1923580 / 8 = 240447.5 > That would be bit too aggressive. I would say set it around 200K to leave room for odd stuff. Rest seems fine with your configuration. Of course a latest version of postgresql is always good though.. Bye Shridhar -- Power is danger.-- The Centurion, "Balance of Terror", stardate 1709.2 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] inferior SCSI performance
I have been experimenting with a new Seagate Cheetah 10k-RPM SCSI to compare with a cheaper Seagate Barracuda 7200-RPM IDE (each in a single-drive configuration). The Cheetah definately dominates the generic IO tests such as bonnie++, but fares poorly with pgbench (and other postgresql operations). I don't understand why switching to a SCSI drive in an otherwise identical setup would so seriously degrade performance. I would have expected the opposite. vmstat does not reveal (to me) any bottlenecks in the SCSI configuration. The only difference between the two test scenarios is that I stopped the postmaster, copied the data dir to the other drive and put a symlink to point to the new path. I ran the tests several times, so these are not flukes. Can anyone explain why this might be happening and how to better leverage this 10k drive? thanks, Mike Adler System info: Box is a Dell 600SC with Adaptec 39160 SCSI controller. Linux 2.4.18-bf2.4 CPU: Intel(R) Celeron(R) CPU 2.00GHz stepping 09 Memory: 512684k/524224k available (1783k kernel code, 11156k reserved, 549k data, 280k init, 0k highmem) postgresql.conf settings: shared_buffers = 1 random_page_cost = 0.3 sort_mem = 4096 ## TEST 1: IDE Seagate Baracuda hde: ST340014A, ATA DISK drive hde: 78165360 sectors (40021 MB) w/2048KiB Cache, CHS=77545/16/63 bonnie++ -f: Version 1.02b --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP dellmar 1G 27001 10 11863 4 20867 3 161.7 0 sample vmstat 1 output during bonnie++: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 0 0 9332 4456 5056 467728 0 0 20864 0 429 698 0 5 95 0 1 1 9332 4380 5056 467728 0 0 5248 27056 361 207 1 4 95 0 1 1 9332 4376 5056 467728 0 0 384 26936 33855 0 0 100 0 1 0 9332 4416 5064 468368 0 0 10112 9764 385 350 0 4 96 1 0 0 9332 4408 5056 468120 0 0 20608 0 427 684 1 7 92 1 0 0 9332 4392 5056 467864 0 0 20992 0 431 692 0 5 95 pgbench: starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 2 number of transactions per client: 400 number of transactions actually processed: 800/800 tps = 110.213013(including connections establishing) tps = 110.563927(excluding connections establishing) sample "vmstat 1" output during pgbench: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 2 0 0160 4348 50032 419320 0 0 240 3432 514 3849 34 7 59 0 2 0160 4392 50764 418544 0 0 224 3348 500 3701 33 6 61 2 0 0160 4364 51652 417688 0 0 240 3908 573 4411 43 8 50 2 0 0160 4364 52508 416832 0 0 160 3708 548 4273 44 8 49 1 1 1160 4420 53332 415944 0 0 160 3604 541 4174 40 13 48 0 1 1160 4420 54160 415120 0 0 104 3552 526 4048 42 14 45 1 0 0160 4964 54720 414576 0 0 128 4328 645 5819 69 7 24 TEST 2: SCSI Drive Seagate Cheetah 10k.6 Vendor: SEAGATE Model: ST336607LWRev: DS08 bonnie++ -f: Version 1.02b --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP dellmar 1G 40249 14 21177 7 46620 7 365.8 0 sample vmstat 1 output during bonnie++: 0 1 1 8916 4400 1844 467216 0 0 384 42348 47580 0 0 100 0 1 1 8916 4392 1844 467216 0 0 512 46420 472 103 0 2 98 1 0 0 8916 4364 1852 469392 0 0 7168 26552 507 268 0 3 97 1 0 0 8916 4452 1868 469392 0 0 28544 12312 658 947 1 15 84 1 0 0 8916 4416 1860 46 0 0 47744 4 850 1534 0 18 82 1 0 0 8916 4436 1796 468312 0 0 48384 0 859 1555 0 19 81 1 0 0 8916 4452 1744 467724 0 0 48640 0 863 1569 2 20 78 pgbench (sounds thrashy): starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 2 number of transactions per client: 400 number of transactions actually processed: 800/800 tps = 33.274922(including connections establishing) tps = 33.307125(excluding connections establishing) sample "vmstat 1" output during pgbench: procs memoryswap io system cpu r
Re: [PERFORM] inferior SCSI performance
Michael Adler <[EMAIL PROTECTED]> writes: > I have been experimenting with a new Seagate Cheetah 10k-RPM SCSI to > compare with a cheaper Seagate Barracuda 7200-RPM IDE (each in a > single-drive configuration). The Cheetah definately dominates the generic > IO tests such as bonnie++, but fares poorly with pgbench (and other > postgresql operations). It's fairly common for ATA drives to be configured to lie about write completion (ie, claim write-complete as soon as data is accepted into their onboard RAM buffer), whereas SCSI drives usually report write complete only when the data is actually down to disk. The performance differential may thus be coming at the expense of reliability. If you run Postgres with fsync off, does the differential go away? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Is there a reason _not_ to vacuum continuously?
I'm running a load of stress scripts against my staging environment to simulate user interactions, and watching the various boxen as time goes by. I noticed that the CPU utilisation on the DB server (PG 7.2.3, RH7.3, Dual PII 550MHz, 1GB RAM, 1GB database on disk, Single 10k SCSI drive) was increasing over time, and manually launched a vacuum analyze verbose. A typical output from the VAV is: NOTICE: --Relation mobilepm-- NOTICE: Index mobilepm_ownerid_idx: Pages 1103; Tuples 32052: Deleted 46012. CPU 0.15s/0.66u sec elapsed 14.82 sec. NOTICE: Index mobilepm_id_idx: Pages 1113; Tuples 32143: Deleted 46012. CPU 0.33s/1.08u sec elapsed 45.89 sec. NOTICE: Index mobilepm_ownerid_status_idx: Pages 1423; Tuples 32319: Deleted 46 012. CPU 0.52s/1.05u sec elapsed 54.59 sec. NOTICE: Index mobilepm_number_idx: Pages 1141; Tuples 32413: Deleted 46012. CPU 0.26s/0.61u sec elapsed 16.13 sec. NOTICE: Removed 46012 tuples in 2548 pages. CPU 0.88s/0.79u sec elapsed 75.57 sec. NOTICE: Pages 3188: Changed 10, Empty 0; Tup 32007: Vac 46012, Keep 11, UnUsed 0. Total CPU 2.56s/4.25u sec elapsed 216.50 sec. NOTICE: --Relation pg_toast_112846940-- NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0. Total CPU 0.00s/0.00u sec elapsed 0.00 sec. NOTICE: Analyzing mobilepm So you can see that some tables are seeing a hell of a lot of updates. That's life, and yes, I do need all those indexes :-) Now I see no drop in performance while the VAV is running, the CPU utilisation gradually drops from 80% to 30% on the DB server, and life in general improves. On the live server (PG 7.2.3, RH7.3, Quad Xeon 700Mhz 1MB cache, 4Gb RAM, 256MB write-back RAID10 over 4 10K disks) I vacuum analyze daily, and vacuum analyze a couple of key tables every 15 minutes, but my question is... *** THE QUESTION(S) *** Is there any reason for me not to run continuous sequential vacuum analyzes? At least for the 6 tables that see a lot of updates? I hear 10% of tuples updated as a good time to vac-an, but does my typical count of 3 indexes per table affect that? Cheers Matt Postscript: I may have answered my own question while writing this mail. Under the current stress test load about 10% of the key tables' tuples are updated between sequential vacuum-analyzes, so the received wisdom on intervals suggests '0' in my case anyway... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Is there a reason _not_ to vacuum continuously?
On Wed, 17 Sep 2003, Matt Clark wrote: > *** THE QUESTION(S) *** > Is there any reason for me not to run continuous sequential vacuum analyzes? > At least for the 6 tables that see a lot of updates? > I hear 10% of tuples updated as a good time to vac-an, but does my typical > count of 3 indexes per table affect that? Generally, the only time continuous vacuuming is a bad thing is when you are I/O bound. If you are CPU bound, then continuous vacuuming is usually acceptable. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] restore time: sort_mem vs. checkpoing_segments
On Mon, 2003-09-15 at 15:15, Vivek Khera wrote:
> And the winner is... checkpoint_segments.
>
> Restore of a significanly big database (~19.8GB restored) shows nearly
> no time difference depending on sort_mem when checkpoint_segments is
> large. There are quite a number of tables and indexes. The restore
> was done from a pg_dump -Fc dump of one database.
>
> All tests with 16KB page size, 30k shared buffers, sort_mem=8192, PG
> 7.4b2 on FreeBSD 4.8.
hmm... i wonder what would happen if you pushed your sort_mem higher...
on some of our development boxes and upgrade scripts, i push the
sort_mem to 102400 and sometimes even higher depending on the box. this
really speeds up my restores quit a bit (and is generally safe as i make
sure there isn't any other activity going on at the time)
another thing i like to do is turn of fsync, as if the system crashes in
the middle of reload i'm pretty sure i'd be starting all over anyway...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PERFORM] Is there a reason _not_ to vacuum continuously?
Matt, > Is there any reason for me not to run continuous sequential vacuum analyzes? > At least for the 6 tables that see a lot of updates? No. You've already proven that the performance gain on queries offsets the loss from the vacuuming. There is no other "gotcha". However: 1) You may be able to decrease the required frequency of vacuums by adjusting your FSM_relations parameter. Have you played with this at all? The default is very low. 2) Are you sure that ANALYZE is needed? Vacuum is required whenever lots of rows are updated, but analyze is needed only when the *distribution* of values changes significantly. 3) using PG 7.3 or less, you will also need to REINDEX these tables+indexes often (daily?). This issue will go away in 7.4, which should make you an early adopter of 7.4. > I hear 10% of tuples updated as a good time to vac-an, but does my typical > count of 3 indexes per table affect that? Not until 7.4. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Is there a reason _not_ to vacuum continuously?
Yes, that makes sense. My worry is really the analyzes. I gather/imagine that: 1) Indexes on fields that are essentially random gain little from being analyzed. 2) Fields that increase monotonically with insertion order have a problem with index growth in 7.2. There may be a performance issue connected with this, although indexes on these fields also gain little from analysis. So if I can't vacuum full I'm SOL anyway and should upgrade to 7.4.1 when available? Further data: When I run a vacuum analyze my app servers do see an increase in response time from PG, even though the DB server is under no more apparent load. I can only assume some kind of locking issue. Is that fair? M > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of > scott.marlowe > Sent: 17 September 2003 20:55 > To: Matt Clark > Cc: [EMAIL PROTECTED] > Subject: Re: [PERFORM] Is there a reason _not_ to vacuum continuously? > > > On Wed, 17 Sep 2003, Matt Clark wrote: > > > *** THE QUESTION(S) *** > > Is there any reason for me not to run continuous sequential > vacuum analyzes? > > At least for the 6 tables that see a lot of updates? > > I hear 10% of tuples updated as a good time to vac-an, but does > my typical > > count of 3 indexes per table affect that? > > Generally, the only time continuous vacuuming is a bad thing is when you > are I/O bound. If you are CPU bound, then continuous vacuuming > is usually > acceptable. > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] restore time: sort_mem vs. checkpoing_segments
> "RT" == Robert Treat <[EMAIL PROTECTED]> writes: RT> hmm... i wonder what would happen if you pushed your sort_mem higher... RT> on some of our development boxes and upgrade scripts, i push the RT> sort_mem to 102400 and sometimes even higher depending on the box. this RT> really speeds up my restores quit a bit (and is generally safe as i make RT> sure there isn't any other activity going on at the time) Ok... just two more tests to run, no big deal ;-) RT> another thing i like to do is turn of fsync, as if the system crashes in RT> the middle of reload i'm pretty sure i'd be starting all over anyway... I'll test it and see what happens. I suspect not a big improvement on a hardware RAID card with 128Mb backed up cache, though. But never say never! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Is there a reason _not_ to vacuum continuously?
> 2) Are you sure that ANALYZE is needed? Vacuum is required > whenever lots of > rows are updated, but analyze is needed only when the *distribution* of > values changes significantly. You are right. I have a related qn in this thread about random vs. monotonic values in indexed fields. > 3) using PG 7.3 or less, you will also need to REINDEX these > tables+indexes > often (daily?). This issue will go away in 7.4, which should > make you an > early adopter of 7.4. I understand this needs an exclusive lock on the whole table, which is simply not possible more than once a month, if that... Workarounds/hack suggestions are more than welcome :-) Ta M ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] How to force an Index ?
Hi list,
I have a table like this:
CREATE TABLE "gsames00" (
"ano_mes" varchar(6) NOT NULL,
"descricao" varchar(30),
PRIMARY KEY ("ano_mes")
);
and an index like this:
CREATE INDEX GSAMES01 ON GSAMES00 (ANO_MES);
When I run a explain analyze with this where clause:
... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'MM') AND ...
ftnfco00.data_emissao is a timestamp. When I run the explain analyze it says:
...
-> Seq Scan on gsames00 (cost=1.00..10006.72 rows=372 width=10)
(actual time=0.01..0.96 rows=372 loops=19923)
...
So it is not using the index, and it makes the query too slow to return the
result. If a run the same query without this clause it gets about 1 minute
faster. You you're wondering : If you can run this query without this clause,
Why don't you take it out ?
I must use it because this query is created by a BI software and to
change it, I'll have to make a lot of changes in the BI software source. In the
Oracle DB it works fine 'cuz Oracle use the index and do it instantly.
Any suggestion on how to force PostgreSQL to use this index ???
I run Vaccum Full Analyze many time before posting this ...
Here follow the whole query and the whole explain:
Query:
SELECT /*+ */
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.qtde_duzias,0)), '+', NVL(ftnfpr00.qtde_duzias,0), 0) ) ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.vlr_liquido,0)), '+',
NVL(ftnfpr00.vlr_liquido,0), 0) ) ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto ,
ftprod00.descricao_produto ,
DIVIDE( SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)
*ftnfpr00.margem_comercial ),
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.qtde_duzias,0), 0 ) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.vlr_liquido,0), 0 ) )
FROM
ftprod00 ,
ftnfco00 ,
ftcgma00 ,
ftcgca00 ,
ftspro00 ,
ftclcr00 ,
gsames00 ,
ftcofi00 ,
ftrepr00 ,
gsesta00 ,
ftsupv00 ,
ftgrep00 ,
ftclgr00 ,
ftband00 ,
fttcli00 ,
ftredc00 ,
ftnfpr00
WHERE
ftnfco00.emp = 909 AND
ftnfpr00.fil IN ('101') AND
ftnfco00.situacao_nf = 'N' AND
ftnfco00.data_emissao >= CAST('01-JAN-2003' AS DATE) AND
ftnfco00.data_emissao <= CAST('31-MAR-2003' AS DATE) AND
ftcofi00.grupo_faturamento >= '01' AND
(ftcofi00.atual_fatura IN ('+','-') OR ftcofi00.nf_prodgratis = 'S') AND
ftcgma00.emp= ftprod00.emp AND
ftcgma00.fil= ftprod00.fil AND
ftcgma00.codigo = ftprod00.cla_marca AND
ftcgca00.emp= ftprod00.emp AND
ftcgca00.fil= ftprod00.fil AND
ftcgca00.codigo = ftprod00.cla_categoria AND
ftspro00.emp= ftprod00.emp AND
ftspro00.fil= ftprod00.fil AND
ftspro00.codigo = ftprod00.situacao AND
ftclcr00.emp = ftnfco00.emp AND
ftclcr00.fil = ftnfco00.empfil AND
ftclcr00.tipo_cadastro = ftnfco00.tipo_cad_clicre AND
ftclcr00.codigo= ftnfco00.cod_cliente AND
gsames00.ano_mes = TO_CHAR(ftnfco00.data_emissao,'MM') AND
ftcofi00.emp = ftnfco00.emp AND
ftcofi00.fil = ftnfco00.empfil AND
ftcofi00.codigo_fiscal = ftnfco00.cod_fiscal AND
ftrepr00.emp = ftnfco00.emp AND
ftrepr00.fil = ftnfco00.empfil AND
ftrepr00.codigo_repr = ftnfco00.cod_repres AND
gsesta00.estado_sigla = ftnfco00.estado_cliente AND
ftsupv00.emp = ftrepr00.emp AND
ftsupv00.fil = ftrepr00.fil AND
ftsupv00.codigo_supervisor = ftrepr00.codigo_supervisor AND
ftgrep00.emp = ftrepr00.emp AND
ftgrep00.fil = ftrepr00.fil AND
ftgrep00.codigo_grupo_rep = ftrepr00.codigo_grupo_rep AND
ftclgr00.emp = ftclcr00.emp AND
ftclgr00.fil = ftclcr00.fil AND
ftclgr00.codigo= ftclcr00.codigo_grupo_cliente AND
ftband00.emp = ftclcr00.emp AND
ftband00.fil = ftclcr00.fil AND
ftband00.codigo= ftclcr00.bandeira_cliente AND
fttcli00.emp = ftclcr00.emp AND
fttcli00.fil = ftclcr00.fil AND
fttcli00.cod_tipocliente = ftclcr00.codigo_tipo_cliente AND
ftredc00.emp = ftclcr00.emp AND
ftredc00.fil = ftclcr00.fil AND
ftredc00.tipo_contribuinte = ftclcr00.tipo_contribuinte AND
ftredc00.codigo_rede = ftclcr00.codigo_rede AND
gsesta00.estado_sigla = ftclcr00.emp_estado AND
ftnfco00.emp = ftnfpr00.emp AND
ftnfco00.fil = ftnfpr00.fil AND
ftnfco00.nota_fiscal = ftnfpr00.nota_fiscal AND
ftnfco00.serie = ftnfpr00.serie AND
ftnfco00.data_emissao = ftnfpr00.data_emissao AND
ftprod00.emp = ftnfpr00.emp AND
ftprod00.fil = ftnfpr00.empfil AND
ftprod00.tipo_cadastro = ftnfpr00.tip
Re: [PERFORM] Is there a reason _not_ to vacuum continuously?
Matt, > I understand this needs an exclusive lock on the whole table, which is > simply not possible more than once a month, if that... Workarounds/hack > suggestions are more than welcome :-) Would it be reasonable to use partial indexes on the table? -- -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] How to force an Index ?
Rhaoni, First off, thanks for posting such complete info. >... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'MM') AND ... > >ftnfco00.data_emissao is a timestamp. When I run the explain analyze it says: > > ... > -> Seq Scan on gsames00 (cost=1.00..10006.72 rows=372 width=10) > (actual time=0.01..0.96 rows=372 loops=19923) > ... Your problem is that you're comparing against a calculated expression based on ftnfco00, which is being filtered in about 18 other ways. As a result, the planner doesn't know what to estimate (see the cost estimate of 1, which is a "blind guess" values) and goes for a seq scan. Can I ask you to try this workaround, to create an expressional index on ftnfco00 (assuming that data_emmisao is of type DATE) create function date_to_mm( date ) returns text as 'select to_char($1, ''MM''); ' language sql immutable strict; create index idx_data_mm on ftnfco00(date_to_mm(data_emmisao)); -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Is there a reason _not_ to vacuum continuously?
Oops! [EMAIL PROTECTED] (Josh Berkus) was seen spray-painting on a wall:
>> I understand this needs an exclusive lock on the whole table, which is
>> simply not possible more than once a month, if that... Workarounds/hack
>> suggestions are more than welcome :-)
>
> Would it be reasonable to use partial indexes on the table?
Dumb question...
... If you create a partial index, does this lock the whole table
while it is being built, or only those records that are affected by
the index definition?
I expect that the answer to that is "Yes, it locks the whole table,"
which means that a partial index won't really help very much, except
insofar as you might, by having it be restrictive in range, lock the
table for a somewhat shorter period of time.
An alternative that may or may not be viable would be to have a series
of tables:
create table t1 ();
create table t2 ();
create table t3 ();
create table t4 ();
Then create a view:
create view t as select * from t1 union all select * from t2 union
all select * from t13 union all select * from t4;
Then you set this view to be updatable, by having a function that
rotates between the 4 tables based on a sequence.
You do SELECT NEXTVAL('t_controller') and the entries start flooding
into t2 rather than t1, or into t3, or into t4, and after t4, they go
back into t1.
When you need to reindex t1, you switch over to load entries into t2,
do maintenance on t1, and then maybe roll back to t1 so you can do the
same maintenance on t2.
--
If this was helpful, rate me
http://www3.sympatico.ca/cbbrowne/lisp.html
Linux is like a Vorlon. It is incredibly powerful, gives terse,
cryptic answers and has a lot of things going on in the background.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[PERFORM] rewrite in to exists?
I'm on 7.3.4 and this query gets horrible performance. Is there a way to rewrite it with an exists or some way to get better performance? select code, id, name, date_of_service from tbl where date_of_service in (select date_of_service from tbl where xxx >= '29800' and xxx <= '29909' and code = 'XX') and client_code = 'XX' order by id, date_of_service; Thanks! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] rewrite in to exists?
> I'm on 7.3.4 and this query gets horrible performance. Is there a way to rewrite it with an exists or some way to get better performance? > > select code, id, name, date_of_service > from tbl > where date_of_service in > (select date_of_service > from tbl > where xxx >= '29800' > and xxx <= '29909' > and code = 'XX') > and client_code = 'XX' > order by id, date_of_service; Why can't you just go: select code, id, name, date_of_service from tbl where xxx <= 29800 and xx >= 29909 and code='XX' and client_code='XX' order by id, date_of_service; Or use a between clause is nice: select code, id, name, date_of_service from tbl where xxx between 29800 and 29909 and code='XX' and client_code='XX' order by id, date_of_service; But seriously - your query above is referencing 'tbl' twice - is that correct, or is the tbl in the subselect supposed to be something different? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
