Re: [PERFORM] Why do my hash joins turn to nested loops?
On Thu, 21 Aug 2008, Tom Lane wrote: > I think you need to raise from_collapse_limit and/or > join_collapse_limit. Ahah, that was it.. a much simpler solution than I was fearing. I had already re-written the queries to get around it, but ran into another snag with that method, so this was good timing. Thanks! -- Ian Smith www.ian.org -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Why do my hash joins turn to nested loops?
ec_uid = 4) -> Index Scan using type_pkey on type (cost=0.00..0.27 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=3555) Index Cond: ((logical.type)::integer = type.uid) -> Index Scan using company_pkey on company (cost=0.00..0.27 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=3555) Index Cond: (model.company_uid = company.uid) Total runtime: 41773.972 ms (33 rows) -- Ian Smith www.ian.org -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] tsearch2 headline and postgresql.conf
Oleg Bartunov wrote:
You didn't provides us any query with explain analyze.
Just to make sure you're fine.
Oleg
On Sun, 22 Jan 2006, [EMAIL PROTECTED] wrote:
Hi folks,
I'm not sure if this is the right place for this but thought I'd
ask. I'm relateively new to postgres having only used it on 3
projects and am just delving into the setup and admin for the second
time.
I decided to try tsearch2 for this project's search requirements but
am having trouble attaining adequate performance. I think I've
nailed it down to trouble with the headline() function in tsearch2.
In short, there is a crawler that grabs HTML docs and places them in
a database. The search is done using tsearch2 pretty much installed
according to instructions. I have read a couple online guides
suggested by this list for tuning the postgresql.conf file. I only
made modest adjustments because I'm not working with top-end hardware
and am still uncertain of the actual impact of the different
paramenters.
I've been learning 'explain' and over the course of reading I have
done enough query tweaking to discover the source of my headache
seems to be headline().
On a query of 429 documents, of which the avg size of the stripped
down document as stored is 21KB, and the max is 518KB (an anomaly),
tsearch2 performs exceptionally well returning most queries in about
100ms.
On the other hand, following the tsearch2 guide which suggests
returning that first portion as a subquery and then generating the
headline() from those results, I see the query increase to 4 seconds!
This seems to be directly related to document size. If I filter out
that 518KB doc along with some 100KB docs by returning "substring(
stripped_text FROM 0 FOR 5) AS stripped_text" I decrease the time
to 1.4 seconds, but increase the risk of not getting a headline.
Seeing as how this problem is directly tied to document size, I'm
wondering if there are any specific settings in postgresql.conf that
may help, or is this just a fact of life for the headline()
function? Or, does anyone know what the problem is and how to
overcome it?
Regards,
Oleg
_
Hi Oleg,
Thanks for taking time to look at this. Pardon my omission, I was
writing that email rather late at night.
The following results from 'explain analyze' are from my
devlopment machine which is a dual PIII 600MHz running Debian
Linux and Postgres 8.1.2. 512 MB RAM. The production machine
yields similar results but it is a virtual server so the
resources are rather unpredictable. It is a quad processor and
has a larger result set in it's DB.
The original query is:
explain analyze
SELECT url, title, headline(stripped_text,q,
'MaxWords=75, MinWords=25,
StartSel=!!!REPLACE_ME!!!,StopSel=!!!/REPLACE_ME!!!'),
rank, to_char(timezone('CST', date_last_changed), 'DD Mon ') AS
date_last_changed
FROM
( SELECT url_id, url, title, stripped_text, date_last_changed, q,
rank(index_text, q) AS rank
FROM (web_page w LEFT JOIN url u USING (url_id)),
to_tsquery('big&search') AS q
WHERE (index_text <> '') AND (index_text @@ q) AND (w.url_id NOT IN (1,2))
AND (url NOT LIKE '%badurl.com%')
ORDER BY rank DESC, date_last_changed DESC
LIMIT 10 OFFSET 0
) AS useless
;
...and the resultant output of EXPLAIN ANALYZE is:
Subquery Scan useless (cost=8.02..8.04 rows=1 width=624) (actual
time=769.131..2769.320 rows=10 loops=1)
-> Limit (cost=8.02..8.02 rows=1 width=282) (actual
time=566.798..566.932 rows=10 loops=1)
-> Sort (cost=8.02..8.02 rows=1 width=282) (actual
time=566.792..566.870 rows=10 loops=1)
Sort Key: rank(w.index_text, q.q), w.date_last_changed
-> Nested Loop (cost=2.00..8.01 rows=1 width=282)
(actual time=4.068..563.128 rows=178 loops=1)
-> Nested Loop (cost=2.00..4.96 rows=1 width=221)
(actual time=3.179..388.610 rows=179 loops=1)
-> Function Scan on q (cost=0.00..0.01
rows=1 width=32) (actual time=0.025..0.028 rows=1 loops=1)
-> Bitmap Heap Scan on web_page w
(cost=2.00..4.94 rows=1 width=189) (actual time=3.123..387.547 rows=179
loops=1)
Filter: ((w.index_text <> ''::tsvector)
AND (w.url_id <> 1) AND (w.url_id <> 2) AND (w.index_text @@ "outer".q))
-> Bitmap Index Scan on
idx_index_text (cost=0.00..2.00 rows=1 width=0) (actual
time=1.173..1.173 rows=277 loops=1)
Index Cond: (w.index_text @@
"outer".q)
-> Index Scan using pk_url on url u
(cost=0.00..3.03 rows=1 width=65) (actual time=0.044..0.049 rows=1
loops=179)
Index Cond: ("outer".url_id = u.url_id)
Filter: (url !~~ '%badurl.com%'::text)
Total runtime: 2771.023 ms
(15 rows)
-
[PERFORM] tsearch2 headline and postgresql.conf
Hi folks, I'm not sure if this is the right place for this but thought I'd ask. I'm relateively new to postgres having only used it on 3 projects and am just delving into the setup and admin for the second time. I decided to try tsearch2 for this project's search requirements but am having trouble attaining adequate performance. I think I've nailed it down to trouble with the headline() function in tsearch2. In short, there is a crawler that grabs HTML docs and places them in a database. The search is done using tsearch2 pretty much installed according to instructions. I have read a couple online guides suggested by this list for tuning the postgresql.conf file. I only made modest adjustments because I'm not working with top-end hardware and am still uncertain of the actual impact of the different paramenters. I've been learning 'explain' and over the course of reading I have done enough query tweaking to discover the source of my headache seems to be headline(). On a query of 429 documents, of which the avg size of the stripped down document as stored is 21KB, and the max is 518KB (an anomaly), tsearch2 performs exceptionally well returning most queries in about 100ms. On the other hand, following the tsearch2 guide which suggests returning that first portion as a subquery and then generating the headline() from those results, I see the query increase to 4 seconds! This seems to be directly related to document size. If I filter out that 518KB doc along with some 100KB docs by returning "substring( stripped_text FROM 0 FOR 5) AS stripped_text" I decrease the time to 1.4 seconds, but increase the risk of not getting a headline. Seeing as how this problem is directly tied to document size, I'm wondering if there are any specific settings in postgresql.conf that may help, or is this just a fact of life for the headline() function? Or, does anyone know what the problem is and how to overcome it? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] fsync vs open_sync
>> OSDL did some testing and found Ext3 to be perhaps the worst FS for >> PostgreSQL >> -- although this testing was with the default options. Ext3 involved > an >> almost 40% write performance penalty compared with Ext2, whereas the >> penalty >> for ReiserFS and JFS was less than 10%. >> >> This concurs with my personal experience. > > I'm really curious to see if you guys have compared insert performance > results between 7.4 and 8.0. As you probably know the system sync() > call was replaced with a looping fsync on open file handles. This may > have some interesting interactions with the WAL sync method. > > What caught my attention initially was the 300+/sec insert performance. > On 8.0/NTFS/fsync=on, I can't break 100/sec on a 10k rpm ATA disk. My > hardware seems to be more or less in the same league as psql's, so I was > naturally curious if this was a NT/Unix issue, a 7.4/8.0 issue, or a > combination of both. The system on which I can get 300 inserts per second is a battery backed up XEON system with 512M RAM, a Promise PDC DMA ATA card, and some fast disks with write caching enabled. (We are not worried about write caching because we have a UPS. Since all non-redundent systems are evaluated on probability of error, we decided that the probability of power failure and UPS failure was sufficiently more rare than system crash with file system corruption or hard disk failure.) > > A 5ms seek time disk would be limited to 200 transaction commits/sec if > each transaction commit has at least 1 seek. Are there some > circumstances where a transaction commit does not generate a physical > seek? > > Maybe ext3 is not the worst filesystem after all! > > Merlin > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [HACKERS] fsync vs open_sync
> Guys, just so you know: > > OSDL did some testing and found Ext3 to be perhaps the worst FS for > PostgreSQL > -- although this testing was with the default options. Ext3 involved an > almost 40% write performance penalty compared with Ext2, whereas the > penalty > for ReiserFS and JFS was less than 10%. > > This concurs with my personal experience. I had exactly the same experience ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] fsync vs open_sync
>> Anyway, with fsync enabled using standard fsync(), I get roughly > 300-400 >> inserts per second. With fsync disabled, I get about 7000 inserts per >> second. When I re-enable fsync but use the open_sync option, I can get >> about 2500 inserts per second. > > You are getting 300-400 inserts/sec with fsync on? If you don't mind me > asking, what's your hardware? (also, have you checked fsync on #s with > the new bgwriter in 7.5?) > 300 inserts persecond with fsync on using fdatasync. 2500 inserts per second with fsync on using open_sync. [EMAIL PROTECTED] mwoodward]$ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 15 model : 2 model name : Intel(R) Xeon(TM) CPU 2.40GHz stepping: 5 cpu MHz : 2399.373 cache size : 512 KB fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 2 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid bogomips: 4784.12 Linux node1 2.4.25 #1 Mon Mar 22 13:33:41 EST 2004 i686 i686 i386 GNU/Linux ide2: BM-DMA at 0xc400-0xc407, BIOS settings: hde:pio, hdf:pio hde: Maxtor 6Y200P0, ATA DISK drive hde: attached ide-disk driver. hde: host protected area => 1 hde: 398297088 sectors (203928 MB) w/7936KiB Cache, CHS=24792/255/63, UDMA(100) PDC20268: IDE controller at PCI slot 06:05.0 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Sun performance - Major discovery!
In message <[EMAIL PROTECTED]>, Jeff writes: I'll go run the regression test suite with my gcc -O2 pg and the suncc pg. See if they pass the test. My default set of gcc optimization flags is: -O3 -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -falign-functions -mcpu=i686 -march=i686 Obviously the last two flags product CPU specific code, so would have to differ...autoconf is always possible, but so is just lopping them off. I have found these flags to produce faster code that a simple -O2, but I understand the exact combination which is best for you is code-dependent. Of course, if you are getting really excited, you can use -fbranch-probabilities, but as you will see if you investigate that requires some profiling information, so is not very easy to actually practically use. -Seth Robertson ---(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] Performance advice
On Wed, 25 Jun 2003, Achilleus Mantzios wrote: > What i think would be ideal (helpful/feasible) > is some kind of documentation of the algorithms involved > in the planner/optimizer, along with some pointers > to postgresql.conf parameters where applicable. > > This way we will know > - Why something is happening > - If it is the best plan > - What tuning is possible I agree. In combination with this, I would find case studies very useful. Have the documentation team solicit a few volunteers with different setups (w/r/t db size, db traffic, and hardware). Perhaps these folks are running with the default postgresql.conf or have done little tuning. Via the performance list, work through the tuning process with each volunteer: 1. Gathering information about your setup that affects tuning. 2. Measuring initial performance as a baseline. 3. Making initial adjustments based on your setup. 4. Identifying poorly-written SQL. 5. Identifying poorly-indexed tables. 6. Measuring effects of each adjustment, and tuning accordingly. (Note: I am certainly no performance expert -- these steps are meant to be examples only.) Solicit a list member to monitor the discussion and document each case study in a consistent fashion. Run completed case studies by the performance and docs lists for review. I would be happy to join the docs team to work on such a project. michael p.s. Should this discussion be moved to psgql-docs? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
