pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing a query
8+ messages / 6 participants
[nested] [flat]
* Optimizing a query
@ 2006-12-13 18:48 James Cloos <cloos@jhcloos.com>
2006-12-13 19:25 ` Re: Optimizing a query Tomeh, Husam <htomeh@firstam.com>
2006-12-13 21:48 ` Re: Optimizing a query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: James Cloos @ 2006-12-13 18:48 UTC (permalink / raw)
To: pgsql-performance
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 <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing a query
2006-12-13 18:48 Optimizing a query James Cloos <cloos@jhcloos.com>
@ 2006-12-13 19:25 ` Tomeh, Husam <htomeh@firstam.com>
2006-12-13 22:35 ` Re: Optimizing a query James Cloos <cloos@jhcloos.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tomeh, Husam @ 2006-12-13 19:25 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; pgsql-performance
Have you run vacuum/analyze on the table?
--
Husam
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of James Cloos
Sent: Wednesday, December 13, 2006 10:48 AM
To: pgsql-performance@postgresql.org
Subject: [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 <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.
Thank you.
FADLD Tag
**********************************************************************
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing a query
2006-12-13 18:48 Optimizing a query James Cloos <cloos@jhcloos.com>
2006-12-13 19:25 ` Re: Optimizing a query Tomeh, Husam <htomeh@firstam.com>
@ 2006-12-13 22:35 ` James Cloos <cloos@jhcloos.com>
2006-12-13 23:15 ` Re: Optimizing a query Tomeh, Husam <htomeh@firstam.com>
0 siblings, 1 reply; 8+ messages in thread
From: James Cloos @ 2006-12-13 22:35 UTC (permalink / raw)
To: Tomeh\, Husam <htomeh@firstam.com>; +Cc: pgsql-performance
>>>>> "Husam" == Tomeh, Husam <htomeh@firstam.com> 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 <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing a query
2006-12-13 18:48 Optimizing a query James Cloos <cloos@jhcloos.com>
2006-12-13 19:25 ` Re: Optimizing a query Tomeh, Husam <htomeh@firstam.com>
2006-12-13 22:35 ` Re: Optimizing a query James Cloos <cloos@jhcloos.com>
@ 2006-12-13 23:15 ` Tomeh, Husam <htomeh@firstam.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tomeh, Husam @ 2006-12-13 23:15 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: pgsql-performance
Your nap and nxx columns have character datatype, so you should use
quotes. Try:
explain analyze select * from nanpa where npa='775' and
nxx='473';
If that does not work, you could try to influence the planner's
execution plan to favor index scans over sequential scan by tweaking a
couple of the postgres parameters, particularly, the
effective_cache_size. This parameter primarily set the planner's
estimates of the relative likelihood of a particular table or index
being in memory, and will thus have a significant effect on whether the
planner chooses indexes over seqscans. Tweaking such parameters are
usually done as a last resort.
--
Husam
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of James Cloos
Sent: Wednesday, December 13, 2006 2:35 PM
To: Tomeh, Husam
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Optimizing a query
>>>>> "Husam" == Tomeh, Husam <htomeh@firstam.com> 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 <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.
Thank you.
FADLD Tag
**********************************************************************
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing a query
2006-12-13 18:48 Optimizing a query James Cloos <cloos@jhcloos.com>
@ 2006-12-13 21:48 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-12-13 21:48 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; +Cc: pgsql-performance
James Cloos <cloos@jhcloos.com> writes:
> ... and was doing queries of the form:
> | select * from nanpa where npa=775 and nxx=413;
If those are char(3) columns, shouldn't you be quoting the constants?
select * from nanpa where npa='775' and nxx='413';
Any non-numeric input will fail entirely without the quotes, and I'm
also not too confident that inputs of less than three digits will work
as you expect (the blank-padding might not match what's in the table).
Leading zeroes would be troublesome too.
OTOH, if the keys are and always will be small integers, it's just
stupid not to be storing them as integers --- integer comparison
is far faster than string.
Postgres' data type capabilities are exceptional. Learn to work with
them, not against them --- that means thinking about what the data
really is and declaring it appropriately.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Optimizing a query
@ 2013-12-18 02:48 Kai Sellgren <kaisellgren@gmail.com>
2013-12-19 17:53 ` Re: Optimizing a query Shaun Thomas <sthomas@optionshouse.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kai Sellgren @ 2013-12-18 02:48 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm new to PostgreSQL and trying to run this query:
SELECT *
FROM "Log"
LEFT JOIN "NewsArticle" ON "NewsArticle".id = "Log"."targetId" AND
"Log"."targetType" = 'NewsArticle'
ORDER BY "Log"."createdAt" DESC
LIMIT 10
Basically I'm finding the last 10 log entries, which point (targetType) to
news articles.
The explain analyze is this:
http://d.pr/i/mZhl (I didn't know how to copy from the pgAdmin, without
having a huge mess)
I have this index on Log:
CREATE INDEX "Log_targetId_targetType_idx"
ON "Log"
USING btree
("targetId", "targetType" COLLATE pg_catalog."default");
I have ran Vacuum and Analyze on both tables.
What am I missing here?
--
Yours sincerely,
Kai Sellgren
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing a query
2013-12-18 02:48 Optimizing a query Kai Sellgren <kaisellgren@gmail.com>
@ 2013-12-19 17:53 ` Shaun Thomas <sthomas@optionshouse.com>
2013-12-24 00:32 ` Re: Optimizing a query Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 1 reply; 8+ messages in thread
From: Shaun Thomas @ 2013-12-19 17:53 UTC (permalink / raw)
To: Kai Sellgren <kaisellgren@gmail.com>; pgsql-performance
On 12/17/2013 08:48 PM, Kai Sellgren wrote:
This is your select:
> SELECT *
> FROM "Log"
> LEFT JOIN "NewsArticle" ON "NewsArticle".id = "Log"."targetId" AND
> "Log"."targetType" = 'NewsArticle'
> ORDER BY "Log"."createdAt" DESC
> LIMIT 10
This is your index:
> CREATE INDEX "Log_targetId_targetType_idx"
> ON "Log"
> USING btree
> ("targetId", "targetType" COLLATE pg_catalog."default");
Unfortunately, this won't help you. You are not matching on any IDs you
indexed, aside from joining against the article table. You have no WHERE
clause to restrict the data set, so it absolutely must read the entire
table to find the most recent records. Without an index on "createdAt",
how is it supposed to know what the ten most recent records are?
Add an index to the createdAt column:
CREATE INDEX idx_log_createdat ON "Log" (createdAt DESC);
Using that, it should get the ten most recent Log records almost
immediately, including associated article content.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Optimizing a query
2013-12-18 02:48 Optimizing a query Kai Sellgren <kaisellgren@gmail.com>
2013-12-19 17:53 ` Re: Optimizing a query Shaun Thomas <sthomas@optionshouse.com>
@ 2013-12-24 00:32 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 8+ messages in thread
From: Mark Kirkwood @ 2013-12-24 00:32 UTC (permalink / raw)
To: Shaun Thomas <sthomas@optionshouse.com>; Kai Sellgren <kaisellgren@gmail.com>; pgsql-performance
On 20/12/13 06:53, Shaun Thomas wrote:
> On 12/17/2013 08:48 PM, Kai Sellgren wrote:
>
> This is your select:
>
>> SELECT *
>> FROM "Log"
>> LEFT JOIN "NewsArticle" ON "NewsArticle".id = "Log"."targetId" AND
>> "Log"."targetType" = 'NewsArticle'
>> ORDER BY "Log"."createdAt" DESC
>> LIMIT 10
>
> This is your index:
>
>> CREATE INDEX "Log_targetId_targetType_idx"
>> ON "Log"
>> USING btree
>> ("targetId", "targetType" COLLATE pg_catalog."default");
>
> Unfortunately, this won't help you. You are not matching on any IDs you
> indexed, aside from joining against the article table. You have no WHERE
> clause to restrict the data set, so it absolutely must read the entire
> table to find the most recent records. Without an index on "createdAt",
> how is it supposed to know what the ten most recent records are?
>
> Add an index to the createdAt column:
>
> CREATE INDEX idx_log_createdat ON "Log" (createdAt DESC);
>
> Using that, it should get the ten most recent Log records almost
> immediately, including associated article content.
>
Also, might be worth creating an index on NewsArticle(id) so that the
join to this table does not require a full table scan:
CREATE INDEX newsarticle_id_idx ON "NewsArticle" (id);
(probably not a problem when you only have a few articles - but will be
as the volume increases over time).
Regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2013-12-24 00:32 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-13 18:48 Optimizing a query James Cloos <cloos@jhcloos.com>
2006-12-13 19:25 ` Tomeh, Husam <htomeh@firstam.com>
2006-12-13 22:35 ` James Cloos <cloos@jhcloos.com>
2006-12-13 23:15 ` Tomeh, Husam <htomeh@firstam.com>
2006-12-13 21:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-12-18 02:48 Optimizing a query Kai Sellgren <kaisellgren@gmail.com>
2013-12-19 17:53 ` Shaun Thomas <sthomas@optionshouse.com>
2013-12-24 00:32 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
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