Re: [PERFORM] Index usage when bitwise operator is used
Hi,
I could not find and normal solution for that issue. But I am using
some workarounds for that issue.
The solution, that I am using now is to create an index for every bit
of your bitmap field.
So something like
CREATE INDEX idx_hobbybit_0_limited
ON "versionA".user_fast_index
USING btree
(gender, dateofbirth) -- here the gender and dateofbirth fields are
the fields that we usually ORDER BY in the select statements, but you
can play with the needed fields
WHERE (hobby_bitmap & 1) > 0;
by creating such an index for every used bit and combining WHERE
(hobby_bitmap & 1 ) > 0 like statements the planner will be choosing
the right index to use.
Another workaround, that will be more applicable in your case I think,
is to create a functional GIN index on your bitmap field using a
static function to create an array of bitmap keys from your bitmap
field.
CREATE OR REPLACE FUNCTION
"versionA".bitmap_to_bit_array(source_bitmap integer)
RETURNS integer[] AS
'select ARRAY( select (1 << s.i) from generate_series(0, 32) as s(i)
where ( 1 << s.i ) & $1 > 0 )'
LANGUAGE 'sql' IMMUTABLE STRICT;
And than create a GIN index on the needed field using this stored
procedure. After that, it would be possible to use intarray set
operators on the result of that function. This will also make it
possible to use that GIN index.
Actually it would be much much better if it were possible to build GIN
indexes directly on the bitmap fields. But this is to be implemented
by GIN and GiST index development team. Probably would be not a bad
idea to make a feature request on them.
With best regards,
Valentine Gogichashvili
On Sep 13, 2:30 pm, [EMAIL PROTECTED] ("W.Alphonse HAROUNY") wrote:
> Hello,
>
> My question is about index usage when bitwise operations are invoked.
> Situation Context:
> --
>
> Lets suppose we have 2 tables TBL1 and TBL2 as the following:
> TBL1 {
> . ;
> integer categoryGroup; // categoryGroup is declared as an index on TABL1
> . ;
>
> }
>
> TBL2 {
> . ;
> integer categoryGroup; // categoryGroup is declared as an index on TABL2
> . ;
>
> }
>
> By conception, I suppose that:
> - [categoryGroup] may hold a limited number of values, less than 32 values.
> - [categoryGroup] is of type integer => it means 4 bytes => 32 bits
> => 32 places available to hold binary '0' or binary '1' values.
> - [categoryGroup] is the result of an "OR bitwise operation" among a
> predefined set of variables [variableCategory].
>We suppose that [variableCategory] is of type integer (=>32 bits)
>and each binary value of [variableCategory] may only hold a single binary
> '1'.
>
> Ex: variableCategory1 = 0010
> variableCategory2 = 0010
> variableCategory3 = 1000
>
> If [categoryGroup] = variableCategory1 | variableCategory2 |
> variableCategory3
> =>[categoryGroup] = 00101010
>
> Question:
> --
> I have an SQL request similar to:
>
> SELECT . FROM TBL1, TBL2 WHERE
> AND
> TBL1.CATEGORY & TBL2.CATEGORY <> 0 //-- where & is the AND bitwise
> operator
>
> Qst:
> 1/ IS the above SQL request will use the INDEX [categoryGroup] defined on
> TBL1 and TBL2 ?
> 2/ What should I do or How should I modify my SQL request in order
>to force the query engine to use an index ? (the already defined index or
> another useful index)
>
> Thx a lot
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [PERFORM] Index usage when bitwise operator is used
> What about saying?: > > TBL1.CATEGORY = TBL2.CATEGORY > Are you sure you understood what was the question? Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY & TBL2.CATEGORY > 0? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] DELETE queries slow down
Hi all, I have a problem with DELETE performance with postgres 7.4. I have a database with 2 great tables (about 150,000 rows) continuously updated, with 1000 - 1200 INSERT per second and 2 or 3 huge DELETE per minute, in which we delete almost all the rows inserted in the 2 tables during the previous minute. I have a single, indexed foreign key between the 2 tables. In this scenario we have always a problem with the delete: For 1 or 2 hours we update only one table, and everything goes ok, where DELETE last at most 6 or 7 seconds. Then for a minute we do INSERT on both table, and everything continue going ok, with DELETE that last about 10 seconds. >From that moment on, DELETES become timeless, and last for 240 and more seconds! Then I can't recover from this state because INSERT continue with the same rate and DELETE become more and more slow. I do a vacuum analyze every minute. What can I do to avoid or at least limit that problem? I will be graceful to everyone who could help me. Hi, Gianluca Internet Email Confidentiality Footer - La presente comunicazione, con le informazioni in essa contenute e ogni documento o file allegato, e' rivolta unicamente alla/e persona/e cui e' indirizzata ed alle altre da questa autorizzata/e a riceverla. Se non siete i destinatari/autorizzati siete avvisati che qualsiasi azione, copia, comunicazione, divulgazione o simili basate sul contenuto di tali informazioni e' vietata e potrebbe essere contro la legge (art. 616 C.P., D.Lgs n. 196/2003 Codice in materia di protezione dei dati personali). Se avete ricevuto questa comunicazione per errore, vi preghiamo di darne immediata notizia al mittente e di distruggere il messaggio originale e ogni file allegato senza farne copia alcuna o riprodurne in alcun modo il contenuto. This e-mail and its attachments are intended for the addressee(s) only and are confidential and/or may contain legally privileged information. If you have received this message by mistake or are not one of the addressees above, you may take no action based on it, and you may not copy or show it to anyone; please reply to this e-mail and point out the error which has occurred. -
Re: [PERFORM] DELETE queries slow down
Galantucci Giovanni wrote: > I have a problem with DELETE performance with postgres 7.4. You should consider upgrading. While I don't recall any particular enhancements that would directly help with this problem, 8.2 is generally faster. > I have a database with 2 great tables (about 150,000 rows) continuously > updated, with 1000 - 1200 INSERT per second and 2 or 3 huge DELETE per > minute, in which we delete almost all the rows inserted in the 2 tables > during the previous minute. > > I have a single, indexed foreign key between the 2 tables. > > > > In this scenario we have always a problem with the delete: > > For 1 or 2 hours we update only one table, and everything goes ok, where > DELETE last at most 6 or 7 seconds. > > Then for a minute we do INSERT on both table, and everything continue > going ok, with DELETE that last about 10 seconds. > > From that moment on, DELETES become timeless, and last for 240 and more > seconds! > > Then I can't recover from this state because INSERT continue with the > same rate and DELETE become more and more slow. I suspect that at first the tables fit in memory, and operations are therefore fast. But after they grow beyond a certain point, they no longer fit in memory, and you start doing I/O which is slow. > I do a vacuum analyze every minute. I'd suggest doing a VACUUM (no analyze) after every DELETE. Have you checked the EXPLAIN ANALYZE output of the DELETE? It might be choosing a bad plan after the table grows. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] DELETE queries slow down
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Galantucci Giovanni wrote: > >> For 1 or 2 hours we update only one table, and everything goes ok, where >> DELETE last at most 6 or 7 seconds. >> >> Then for a minute we do INSERT on both table, and everything continue >> going ok, with DELETE that last about 10 seconds. >> >> From that moment on, DELETES become timeless, and last for 240 and more >> seconds! What do the inserts and deletes actually look like? Are there subqueries or joins or are they just inserting values and deleting simple where clauses? And are these in autocommit mode or are you running multiple commands in a single transaction? Generally it's faster to run more commands in a single transaction but what I'm worried about is that you may have a transaction open which you aren't committing for a long time. This can stop vacuum from being able to clean up dead space and if it's in the middle of a query can actually cause vacuum to get stuck waiting for the query to finish using the page it's using. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] [Again] Postgres performance problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gavin M. Roy escribió: > How many backends do you have at any given time? Have you tried using > something like pgBouncer to lower backend usage? How about your IO > situation? Have you run something like sysstat to see what iowait is > at? backends arround 50 -100 I don't use pgBouncer yet. Sysstat reports veeery low io. Right now Im checking out fsm parameter, as Scott recomended. Seems there is the problem. > > On 9/11/07, Ruben Rubio <[EMAIL PROTECTED]> wrote: > > Hi, > > I having the same problem I told here a few weeks before. Database is > using too much resources again. > > I do a vacumm full each day, but seems it is not working. I am preparing > an update to postgres 8.2.4 (actually I am using at 8.1.3, and tests for > update will need several days) > > Last time I had this problem i solved it stopping website, restarting > database, vacuumm it, run again website. But I guess this is going to > happen again. > > I would like to detect and solve the problem. Any ideas to detect it? > > Thanks in advance, > > > >> >> - ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly >> >> >> -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG7mx7Io1XmbAXRboRAn0VAJ4sGc1KCNlsbrybVbY/WfB+3XWBbwCfb7Z/ WNGyJCRo6zd26uR6FB6SA8o= =SYzs -END PGP SIGNATURE- begin:vcard fn:Ruben Rubio n:Rubio;Ruben org:Rentalia Holidays S.L adr;quoted-printable:;;Gran v=C3=ADa 31, 9=C2=BA-1=C2=BA;Madrid;;;Spain email;internet:[EMAIL PROTECTED] tel;work:+34915233104 url:http://www.rentalia.com version:2.1 end:vcard ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] [Again] Postgres performance problem
On Wed, Sep 12, 2007 at 03:01:12PM -0500, Erik Jones wrote: > > On Sep 12, 2007, at 2:19 PM, Frank Schoep wrote: > > >On Sep 12, 2007, at 9:07 PM, Scott Marlowe wrote: > >>On 9/12/07, Mikko Partio <[EMAIL PROTECTED]> wrote: > >>>? > >>>Aren't you mixing up REINDEX and CLUSTER? > >> > >>? > >>Either one does what a vacuum full did / does, but generally does > >>it better. > > > >On topic of REINDEX / VACUUM FULL versus a CLUSTER / VACUUM ANALYZE > >I'd like to ask if CLUSTER is safe to run on a table that is in > >active use. > > > >After updating my maintenance scripts from a VACUUM FULL (add me to > >the list) to CLUSTER (which improves performance a lot) I noticed I > >was getting "could not open relation ?" errors in the log while the > >scripts ran so I reverted the change. This was on 8.1.9. > > You'd probably see the same behavior on 8.2.x. CLUSTER is not > transactionally safe so you don't want to run CLUSTER on tables that > are actively being used. I believe that's been fixed for 8.3. Actually, that's a bit over-conservative... what happens prior to 8.3 is that CLUSTER rewrites the table using it's XID for everything. That can break semantics for any transactions that are running in serializable mode; if you're just using the default isolation level of read committed, you're fine with CLUSTER. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpJrcbgbZXeA.pgp Description: PGP signature
Re: [PERFORM] Index usage when bitwise operator is used
>>> On Mon, Sep 17, 2007 at 2:49 AM, in message <[EMAIL PROTECTED]>, valgog <[EMAIL PROTECTED]> wrote: >> What about saying?: >> >> TBL1.CATEGORY = TBL2.CATEGORY >> > > Are you sure you understood what was the question? > > Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY & > TBL2.CATEGORY > 0? Yes, given that he stipulated that one and only one bit would be set. -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [Again] Postgres performance problem
On Thu, Sep 13, 2007 at 01:58:10AM -0400, Greg Smith wrote: > On Wed, 12 Sep 2007, Scott Marlowe wrote: > > >I'm getting more and more motivated to rewrite the vacuum docs. I think > >a rewrite from the ground up might be best... I keep seeing people > >doing vacuum full on this list and I'm thinking it's as much because of > >the way the docs represent vacuum full as anything. > > I agree you shouldn't start thinking in terms of how to fix the existing > documentation. I'd suggest instead writing a tutorial leading someone > through what they need to know about their tables first and then going > into how vacuum works based on that data. Take a look at the stuff at http://decibel.org/~decibel/pervasive/, it'd hopefully provide a useful starting point. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgp9kUqIc1Pg6.pgp Description: PGP signature
Re: [PERFORM] Index usage when bitwise operator is used
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > On Mon, Sep 17, 2007 at 2:49 AM, in message > <[EMAIL PROTECTED]>, valgog > <[EMAIL PROTECTED]> wrote:=20 >> Are you sure you understood what was the question? >> >> Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY & >> TBL2.CATEGORY > 0? > Yes, given that he stipulated that one and only one bit would be set. Really? In that case, isn't this bit-field just a bad implementation of an enum-style field? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Index usage when bitwise operator is used
>>> On Mon, Sep 17, 2007 at 8:37 AM, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: >> On Mon, Sep 17, 2007 at 2:49 AM, in message >> <[EMAIL PROTECTED]>, valgog >> <[EMAIL PROTECTED]> wrote:=20 >>> Are you sure you understood what was the question? >>> >>> Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY & >>> TBL2.CATEGORY > 0? > >> Yes, given that he stipulated that one and only one bit would be set. > > Really? In that case, isn't this bit-field just a bad implementation of > an enum-style field? My bad. I did misread it. Sorry, all. -Kevin ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Index usage when bitwise operator is used
Hi, A little clarification. Actually, TBL1.CATEGORY and/or TBL2.CATEGORY may hold a binary value having multiple binary(ies) '1'. Each binary value column represent an business attribute. If a binary value column is equal to '1', it means that the business attribute is True, otherwise it is false. I adopted this avoid defining a detail table to table TBL1. Idem to TBL2. If TBL1.CATEGORY | TBL2.CATEGORY > 0 => it means that we have at least one common business attribute that is TRUE for TBL1 and TBL2. Regards W.Alf On 9/17/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > On Mon, Sep 17, 2007 at 2:49 AM, in message > > <[EMAIL PROTECTED]>, valgog > > <[EMAIL PROTECTED]> wrote:=20 > >> Are you sure you understood what was the question? > >> > >> Is the TBL1.CATEGORY = TBL2.CATEGORY the same as TBL1.CATEGORY & > >> TBL2.CATEGORY > 0? > > > Yes, given that he stipulated that one and only one bit would be set. > > Really? In that case, isn't this bit-field just a bad implementation of > an enum-style field? > >regards, tom lane > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly >
Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?
Hi, Decibel! wrote: Actually, in this case, I suspect that latency will be far more critical than overall bandwidth. I don't know if it's inherent to Gig-E, but my limited experience has been that Gig-E has higher latency than 100mb. I've been looking for some benchmarks, but it's rather hard to find. It looks like people are much more concerned about throughput ?!? However, I'd like to share some of the sites I've found, especially regarding Fast Ethernet vs. Gigabit Ethernet: - Ashford Computer Consulting Service benchmarked five different gigabit ethernet adapters [1], back in 2004. For most cards they measured between ca. 100 - 150 microseconds for a UDP round trip of a token, a so called hot potato benchmark. Unfortunately they didn't compare with Fast Ethernet. - The NetPIPE project has some of it's measurements at the very bottom of it's website [2]. Mostly for high speed and low latency links. Again, Fast Ethernet is missing. The diagram tells the following latencies (in microseconds): 75 10 Gigabit Ethernet 62 Gigabit Ethernet 8 Myrinet 7.5 Infini Band 4.7 Atoll 4.2 SCI I've no explanation for the significantly better measure for gigabit ethernet compared with the above benchmark. From their description I'm concluding that they also measured a round-trip, but not via UDP. The bad value for 10 Gigabit Ethernet is due to a poor Intel adapter, which also has poor throughput. They claim that newer adapters are better. - Finally, I've found a latency comparison between Fast vs Gigabit Ethernet, here [3]. Figure 6, in the second third of the page shows a NetPIPE latency benchmark between Ethernet, Fast Ethernet and Gigabit Ethernet (additionally ATM and FDDI). It looks like Gigabit Ethernet features slightly better latency. From these findings I'm concluding, that commodity Ethernet hardware has quite similar latencies, no matter if you are using Fast, Gigabit or 10 Gigabit Ethernet. If you really want to have a low latency interconnect, you need to pay the extra bucks for specialized, low latency networking hardware (which may still be based on 10GE, see Myrinet's 10GE adapter). If you know other resources, I'd be curious to know. Regards Markus [1]: Ashford Computer Consulting Service, GigE benchmarks: http://www.accs.com/p_and_p/GigaBit/conclusion.html [2]: NetPIPE website: http://www.scl.ameslab.gov/netpipe/ [3]: Gigabit Ethernet and Low-Cost Supercomputing http://www.scl.ameslab.gov/Publications/Gigabit/tr5126.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Query works when kludged, but would prefer "best practice" solution
Hi all,
Please see the section marked as "PROBLEM" in "ORIGINAL QUERY" plan below.
You can see it's pretty slow. Oddly enough, an index for facility_address_id
is available but not being used, but I suspect it's questionable whether it
would be an improvement.
I knew that the filter was best applied to the results of the join - my
attempts to restructure the query with subqueries, etc didn't fool the
planner - it always figured out a plan that had this problem SEQ SCAN +
FILTER in it.
Finally, I "hid" the condition from the planner with a coalesce function -
see "SOLUTION" in the "KLUDGED QUERY" plan below.
Sure enough, a new plan appeared with a remarkable performance improvement!
The purpose of this query is to find facilities within a geographical area
when the complete address data is missing (hence the facility_address_id is
NULL).
PG is 8.4.2 on RH linux server with 1GB ram, HDD is RAID 1.
I don't like kludging like this - so any and all help or advice is
appreciated!
Carlo
ORIGINAL QUERY
select
pp.provider_id,
pp.provider_practice_id,
nearby.distance
from mdx_core.provider_practice as pp
join mdx_core.facility as f
on f.facility_id = pp.facility_id
join (select * from mdx_core.zips_in_mile_range('08820', 10)) as nearby
on f.default_country_code = 'US'
and f.default_postal_code = nearby.zip
where facility_address_id is null
Hash Join (cost=30258.99..107702.53 rows=9438 width=16) (actual
time=169.516..3064.188 rows=872 loops=1)
Hash Cond: (pp.facility_id = f.facility_id)
PROBLEM:
-> Seq Scan on provider_practice pp (cost=0.00..74632.55 rows=724429
width=12) (actual time=0.039..1999.457 rows=728396 loops=1)
Filter: (facility_address_id IS NULL)
-> Hash (cost=29954.15..29954.15 rows=24387 width=12) (actual
time=156.668..156.668 rows=907 loops=1)
-> Nested Loop (cost=0.00..29954.15 rows=24387 width=12) (actual
time=149.891..155.343 rows=907 loops=1)
-> Function Scan on zips_in_mile_range (cost=0.00..12.50
rows=1000 width=40) (actual time=149.850..149.920 rows=66 loops=1)
-> Index Scan using facility_country_postal_code_idx on
facility f (cost=0.00..29.64 rows=24 width=15) (actual time=0.015..0.048
rows=14 loops=66)
Index Cond: ((f.default_country_code = 'US'::bpchar) AND
((f.default_postal_code)::text = zips_in_mile_range.zip))
Total runtime: 3065.338 ms
KLUDGED QUERY
select
pp.provider_id,
pp.provider_practice_id,
nearby.distance
from mdx_core.provider_practice as pp
join mdx_core.facility as f
on f.facility_id = pp.facility_id
join (select * from mdx_core.zips_in_mile_range('08820', 10)) as nearby
on f.default_country_code = 'US'
and f.default_postal_code = nearby.zip
and coalesce(pp.facility_address_id, -1) = -1
Nested Loop (cost=0.00..112618.87 rows=180 width=16) (actual
time=149.680..167.261 rows=872 loops=1)
-> Nested Loop (cost=0.00..29954.15 rows=24387 width=12) (actual
time=149.659..155.018 rows=907 loops=1)
-> Function Scan on zips_in_mile_range (cost=0.00..12.50 rows=1000
width=40) (actual time=149.620..149.698 rows=66 loops=1)
-> Index Scan using facility_country_postal_code_idx on facility f
(cost=0.00..29.64 rows=24 width=15) (actual time=0.015..0.045 rows=14
loops=66)
Index Cond: ((f.default_country_code = 'US'::bpchar) AND
((f.default_postal_code)::text = zips_in_mile_range.zip))
SOLUTION
-
-> Index Scan using provider_practice_facility_idx on provider_practice
pp (cost=0.00..3.38 rows=1 width=12) (actual time=0.007..0.009 rows=1
loops=907)
Index Cond: (f.facility_id = pp.facility_id)
Filter: (COALESCE(facility_address_id, -1) = -1)
-
Total runtime: 168.275 ms
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution
On 9/17/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: > Hi all, > > Please see the section marked as "PROBLEM" in "ORIGINAL QUERY" plan below. > You can see it's pretty slow. Oddly enough, an index for facility_address_id > is available but not being used, but I suspect it's questionable whether it > would be an improvement. This looks like it might be the problem tom caught and rigged a solution to: http://people.planetpostgresql.org/dfetter/index.php?/archives/134-PostgreSQL-Weekly-News-September-03-2007.html (look fro band-aid). If that's the case, the solution is to wait for 8.2.5 (coming soon). merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution
Well, there goes my dream of getting a recommendation that will deliver a blinding insight into how to speed up all of my queries a thousand-fold. Thanks Merlin! -Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] Sent: September 17, 2007 8:03 PM To: Carlo Stonebanks Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution On 9/17/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: > Hi all, > > Please see the section marked as "PROBLEM" in "ORIGINAL QUERY" plan below. > You can see it's pretty slow. Oddly enough, an index for facility_address_id > is available but not being used, but I suspect it's questionable whether it > would be an improvement. This looks like it might be the problem tom caught and rigged a solution to: http://people.planetpostgresql.org/dfetter/index.php?/archives/134-PostgreSQ L-Weekly-News-September-03-2007.html (look fro band-aid). If that's the case, the solution is to wait for 8.2.5 (coming soon). merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution
On 9/17/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: > Well, there goes my dream of getting a recommendation that will deliver a > blinding insight into how to speed up all of my queries a thousand-fold. that's easy...delete your data! :-) merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution
Thanks, it worked. Client happy. Big bonus in the mail. -Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] Sent: September 17, 2007 8:18 PM To: Carlo Stonebanks Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution On 9/17/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: > Well, there goes my dream of getting a recommendation that will deliver a > blinding insight into how to speed up all of my queries a thousand-fold. that's easy...delete your data! :-) merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > On 9/17/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: >> Please see the section marked as "PROBLEM" in "ORIGINAL QUERY" plan below. > This looks like it might be the problem tom caught and rigged a solution to: > http://people.planetpostgresql.org/dfetter/index.php?/archives/134-PostgreSQL-Weekly-News-September-03-2007.html > (look fro band-aid). No, fraid not, that was about misestimation of outer joins, and I see no outer join here. What I do see is misestimation of a set-returning-function's output: -> Function Scan on zips_in_mile_range (cost=0.00..12.50 rows=1000 width=40) (actual time=149.850..149.920 rows=66 loops=1) There's not any very nice way to improve that in existing releases :-(. In 8.3 it will be possible to add a ROWS option to function definitions to replace the default "1000 rows" estimate with some other number, but that still helps little if the number of result rows is widely variable. As far as kluges go: rather than kluging conditions affecting unrelated tables, maybe you could put in a dummy constraint on the function's output --- ie, a condition you know is always true, but the planner won't know that, and will scale down its result-rows estimate accordingly. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Regarding COPY command from Postgres 8.2.0
We have upgraded postgres from 7.2.4 to 8.2.0. We have program which executes COPY command and our new database is changed having some extra columns in some tables. Because of this, COPY commands are failing. So, we wanted the option to COPY the data without specifying column names. Thanks, Sonal On 9/14/07, Ansgar -59cobalt- Wiechers <[EMAIL PROTECTED]> wrote: > > On 2007-09-14 soni de wrote: > > In Postgres 7.2.4, COPY command is working fine even if tables have 6 > > fields but we are copying only 5 fields from the file > > > > But in Postgres 8.2.0, if table has 6 fields and we need to copy data > > for 5 fields only, then we need to specify the column names too in > > COPY command. > > > > Is there any configurable option, so that without specifying column > > name in COPY command we can copy records in table as happened in > > Postgres 7.2.4? > > I don't know if it is possible, but even if it were I'd strongly > recommend against it, as you'd be relying on the order the columns were > created in. That's a rather bad idea IMHO. Why would you want to avoid > giving the names of the columns in the first place? > > Regards > Ansgar Wiechers > -- > "The Mac OS X kernel should never panic because, when it does, it > seriously inconveniences the user." > --http://developer.apple.com/technotes/tn2004/tn2118.html > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution
Hi Tom, Thanks for the suggestion - this concept is pretty new to me. Can you expand a bit on the idea of how to place such a "dummy" constraint on a function, and the conditions on which it affects the planner? Would this require that constraint_exclusion be set on? (When I go to sleep, I have a dream -- and in this dream Tom writes a brilliant three line code sample that makes it all clear to me, and I wake up a PostgreSQL guru) ;-) Carlo -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: September 17, 2007 11:30 PM To: Merlin Moncure Cc: Carlo Stonebanks; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Query works when kludged, but would prefer "best practice" solution "Merlin Moncure" <[EMAIL PROTECTED]> writes: > On 9/17/07, Carlo Stonebanks <[EMAIL PROTECTED]> wrote: >> Please see the section marked as "PROBLEM" in "ORIGINAL QUERY" plan below. > This looks like it might be the problem tom caught and rigged a solution to: > http://people.planetpostgresql.org/dfetter/index.php?/archives/134-PostgreSQ L-Weekly-News-September-03-2007.html > (look fro band-aid). No, fraid not, that was about misestimation of outer joins, and I see no outer join here. What I do see is misestimation of a set-returning-function's output: -> Function Scan on zips_in_mile_range (cost=0.00..12.50 rows=1000 width=40) (actual time=149.850..149.920 rows=66 loops=1) There's not any very nice way to improve that in existing releases :-(. In 8.3 it will be possible to add a ROWS option to function definitions to replace the default "1000 rows" estimate with some other number, but that still helps little if the number of result rows is widely variable. As far as kluges go: rather than kluging conditions affecting unrelated tables, maybe you could put in a dummy constraint on the function's output --- ie, a condition you know is always true, but the planner won't know that, and will scale down its result-rows estimate accordingly. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
