I have a table with 1.3 billion rows partitioned by date_trunc('day',date_col)
When I issue a query for a date that is not present (i.e no partition available for that date)..the query still runs for about 45 seconds before returning 0 rows.
Is this expected behavior? I would expect an instant response assuming partition pruning occurs ( I am comparing with Oracle which expectedly returns the '0 rows' result quickly...although it is terrible for any other heavy query)
Partition Pruning Slow
Moderator: NorbertKrupa
Re: Partition Pruning Slow
Hi!
>> I would expect an instant response assuming partition pruning occurs
And you are right.
>> Is this expected behavior?
No. Check for statistics: run your query with 'EXPLAIN' in the beginning.
Resolve any "NO STATISTICS" or ''PREDICATE VALUE OUT-OF-RANGE" or "STALE STATISTICS".
You can achieve it by running:
>> I would expect an instant response assuming partition pruning occurs
And you are right.
>> Is this expected behavior?
No. Check for statistics: run your query with 'EXPLAIN' in the beginning.
Resolve any "NO STATISTICS" or ''PREDICATE VALUE OUT-OF-RANGE" or "STALE STATISTICS".
You can achieve it by running:
Code: Select all
select analyze_statistics('')
Re: Partition Pruning Slow
Check to make sure that you are actually getting partition pruning.
create table tpart(c1 integer not null) partition by c1;
copy tpart from stdin direct;
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> 9
>> 10
>> \.
dbadmin=> \o | grep Pruned
dbadmin=> explain local verbose select count(*) from tpart where c1=100;
dbadmin=> 3[label = "StorageUnionStep: tpart_super\nsubdivision: 1048576\nstartEpoch 0\nendEpoch 33\nPruned storages: 7 (7 rows)\nnodeSet: [0] \n[Local Segmented Properties: UNSEGMENTED]\nUnc: count(*)[100,-3] - Integer(8)", color = "purple", shape = "box"];
dbadmin=> \o
(The data is segmented and my initiator node has 7 of the partition keys)
--Sharon
create table tpart(c1 integer not null) partition by c1;
copy tpart from stdin direct;
>> 1
>> 2
>> 3
>> 4
>> 5
>> 6
>> 7
>> 8
>> 9
>> 10
>> \.
dbadmin=> \o | grep Pruned
dbadmin=> explain local verbose select count(*) from tpart where c1=100;
dbadmin=> 3[label = "StorageUnionStep: tpart_super\nsubdivision: 1048576\nstartEpoch 0\nendEpoch 33\nPruned storages: 7 (7 rows)\nnodeSet: [0] \n[Local Segmented Properties: UNSEGMENTED]\nUnc: count(*)[100,-3] - Integer(8)", color = "purple", shape = "box"];
dbadmin=> \o
(The data is segmented and my initiator node has 7 of the partition keys)
--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Partition Pruning Slow
I analyzed the table (the original explain showed No Statistics)
After analysis, the query was still slow (maybe a little quicker..but not instant)
Here is the output for the pruning test
explain local verbose select count(*) from tpart where c1=100;
3[label = "StorageUnionStep: tpart_super\nsubdivision: 1048576\nstartEpoch 0\ne ndEpoch 481\nPruned storages: 10 (10 rows)\nnodeSet: [0] \n[Local Segmented Prop erties: UNSEGMENTED]\nUnc: count(*)[100,-3] - Integer(8)", color = "purple", sha pe = "box"];
Does that mean partition pruning occurs ?
After analysis, the query was still slow (maybe a little quicker..but not instant)
Here is the output for the pruning test
explain local verbose select count(*) from tpart where c1=100;
3[label = "StorageUnionStep: tpart_super\nsubdivision: 1048576\nstartEpoch 0\ne ndEpoch 481\nPruned storages: 10 (10 rows)\nnodeSet: [0] \n[Local Segmented Prop erties: UNSEGMENTED]\nUnc: count(*)[100,-3] - Integer(8)", color = "purple", sha pe = "box"];
Does that mean partition pruning occurs ?
Re: Partition Pruning Slow
the pruning is fast on our 3 node clustered setup..but slow on a single node setup.
Re: Partition Pruning Slow
Have you verified that you're getting partition pruning on the single node cluster? The output that you pasted in from the explain was for the example I gave you. What does it show for your real query?
You could PROFILE the query on both the faster and slower clusters, and then compare the clock time counters in EXECUTION_ENGINE_PROFILES.
--Sharon
You could PROFILE the query on both the faster and slower clusters, and then compare the clock time counters in EXECUTION_ENGINE_PROFILES.
--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: Partition Pruning Slow
For my query..the PrunedStorages showed 0 (Number of storages = 21)
15[label = "StorageUnionStep: fw_srcdst\nsubdivision: 1048576\nstartEpoch 0\nendEpoch 603\nthreads 16\nNo of storages: 21\nPruned storages: 0 (0 rows)\nLocally Resegment By: ((((((100 \<*\> 69069) \<+\> internal_strhash(a.storid)) \<*\> 69069) \<+\> internal_numhash(a.tempid)) \<*\> 69069) \<+\> a.tempid1)\nnodeSet: [0] \n[Local Segmented Properties: SEGMENTED]\nUnc: a.storid[1,4] - Varchar(15)\nUnc: a.tempid[1,10] - Numeric(38,0)\nUnc: a.tempid1[1,13] - Integer(8)", color = "purple", shape = "box"];
15[label = "StorageUnionStep: fw_srcdst\nsubdivision: 1048576\nstartEpoch 0\nendEpoch 603\nthreads 16\nNo of storages: 21\nPruned storages: 0 (0 rows)\nLocally Resegment By: ((((((100 \<*\> 69069) \<+\> internal_strhash(a.storid)) \<*\> 69069) \<+\> internal_numhash(a.tempid)) \<*\> 69069) \<+\> a.tempid1)\nnodeSet: [0] \n[Local Segmented Properties: SEGMENTED]\nUnc: a.storid[1,4] - Varchar(15)\nUnc: a.tempid[1,10] - Numeric(38,0)\nUnc: a.tempid1[1,13] - Integer(8)", color = "purple", shape = "box"];