[PERFORM] Optimizing a query
I've currently got this table: , | n=# \d nanpa | Table "public.nanpa" |Column | Type | Modifiers | +--+--- | state | character(2) | | npa| character(3) | not null | nxx| character(3) | not null | ocn| character(4) | | company| text | | ratecenter | text | | switch | text | | effective | date | | use| character(2) | not null | assign | date | | ig | character(1) | | Indexes: | "nanpa_pkey" PRIMARY KEY, btree (npa, nxx) CLUSTER ` and was doing queries of the form: , | select * from nanpa where npa=775 and nxx=413; ` where were quite slow. Explain showed that it was doing sequential scans even though the primary key contained the two term I was selecting on. Today, looking at it again in prep to this post, I noticed that the numbers were being converted to ::text, and a quick test showed that queries of the form: , | select * from nanpa where npa=775::bpchar and nxx=413::bpchar; ` used the index. I specified char(3) when I created the table simple because npa and nxx are defined as three-character strings. Tagging the queies is a pain, especially as I often do queries of that form in psql(1). (Incidently, there are multiple similar tables, also keyed on (npa,nxx), which show the same problem. The nanpa table above is just a good example.) Should I convert the columns to text? Or create an additional index that expects ::text args? (If so, how?) Or is there some other way to ensure the indices get used w/o having to tag data in the queries? Thanks, -JimC -- James Cloos <[EMAIL PROTECTED]> OpenPGP: 1024D/ED7DAEA6 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Optimizing a query
>>>>> "Husam" == Tomeh, Husam <[EMAIL PROTECTED]> writes: Husam> Have you run vacuum/analyze on the table? Yes, back when I first noticed how slow it was. It did not make any difference. explain analyze says: , | n=# explain analyse select * from nanpa where npa=775 and nxx=473; |QUERY PLAN | | Seq Scan on nanpa (cost=0.00..5344.60 rows=4 width=105) (actual time=371.718..516.816 rows=1 loops=1) |Filter: (((npa)::text = '775'::text) AND ((nxx)::text = '473'::text)) | Total runtime: 516.909 ms | (3 rows) ` vs: , | n=# explain analyse select * from nanpa where npa=775::char and nxx=473::char; | QUERY PLAN | -- | Index Scan using nanpa_pkey on nanpa (cost=0.00..4.33 rows=1 width=105) (actual time=64.831..64.831 rows=0 loops=1) |Index Cond: ((npa = '7'::bpchar) AND (nxx = '4'::bpchar)) | Total runtime: 64.927 ms | (3 rows) ` BTW, I forgot to mention I'm at 8.1.4 on that box. -JimC -- James Cloos <[EMAIL PROTECTED]> OpenPGP: 1024D/ED7DAEA6 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] CPUs for new databases
>>>>> "JB" == Josh Berkus writes: JB> In a general workload, fewer faster cores are better. We do not scale JB> perfectly across cores. The only case where that's not true is JB> maintaining lots of idle connections, and that's really better dealt JB> with in software. I've found that ram speed is the most limiting factor I've run into for those cases where the db fits in RAM. The less efficient lookups run just as fast when the CPU is in powersving mode as in performance, which implies that the cores are mostly waiting on RAM (cache or main). I suspect cache size and ram speed will be the most important factors until the point where disk i/o speed and capacity take over. I'm sure some db applications run computaionally expensive queries on the server, but most queries seem light on computaion and heavy on gathering and comparing. It can help to use recent versions of gcc with -march=native. And recent versions of glibc offer improved string ops on recent hardware. -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- 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] CPU bound
>>>>> "RA" == Royce Ausburn writes: RA> I notice that when restoring a DB on a laptop with an SDD, RA> typically postgres is maxing out a CPU - even during a COPY. The time the CPUs spend waiting on system RAM shows up as CPU time, not as Wait time. It could be just that the SSD is fast enough that the RAM is now the bottleneck, although parsing and text<=>binary conversions (especially for integers, reals and anything stored as an integer) also can be CPU-intensive. -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- 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] CPU bound
>>>>> "MG" == Mladen Gogala writes: MG> Good time accounting is the most compelling reason for having a wait MG> event interface, like Oracle. Without the wait event interface, one MG> cannot really tell where the time is spent, at least not without MG> profiling the database code, which is not an option for a production MG> database. And how exactly, given that the kernel does not know whether the CPU is active or waiting on ram, could an application do so? -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- 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] Linux: more cores = less concurrency.
>>>>> "GA" == Glyn Astill writes: GA> I was hoping someone had seen this sort of behaviour before, GA> and could offer some sort of explanation or advice. Jesper's reply is probably most on point as to the reason. I know that recent Opterons use some of their cache to better manage cache-coherency. I presum recent Xeons do so, too, but perhaps yours are not recent enough for that? -JimC -- James Cloos OpenPGP: 1024D/ED7DAEA6 -- 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] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
>>>>> "GBB" == Graeme B Bell writes: GBB> 1a. For example AMD CPUs list the number of integer cores (e.g. 16), GBB> but there is actually only half as many cores available for floating GBB> point work (8). So if your functions need to use floating point, your GBB> scaling will suffer badly on FP functions. That is half as many 256-bit float units; for scalar math and for 128-bit vector math each core gets a half of the float unit. Only for the 256-bit vector math do the schedulars have to compete for float unit access. -JimC -- James Cloos OpenPGP: 0x997A9F17ED7DAEA6 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
